Let's POWER Automate

From no-code to low-code

Menu
  • Expressions
  • Filters
  • General
  • Application specific solutions
    • Dataverse
    • Excel
    • Forms
    • Planner
    • Outlook
    • SharePoint
    • Teams
  • Triggers
  • Resources
  • ABOUT ME
  • Get help with your flow
Menu

How to combine multiple filters in Get items Filter Query (Power Automate)

Posted on September 12, 2021September 12, 2021 by Tom

“I am able to use each filter independently, but when I try to use multiple filters at once in the Filter Query it doesn’t work, what’s the right syntax in Power Automate?”


The Filter Query in ‘Get items’ action is one of the most powerful tools when working with SharePoint in Power Automate. Instead of processing all the items in your flow, you can take only the relevant ones. It saves API calls, flow run time, and makes maintenance of the flow easier. The filter works often fine when you filter using only a single column, but what if that’s not enough? What if you need to filter by multiple columns at once, e.g. by item status and date. Or by a date and status1 or status2?

Multiple filters with AND

When all of the filters must be true to return the item, you must connect the filters with AND operator. It doesn’t matter if it’s upper case or lower case, only the word and matters. For example, when building a reminder flow for opened tasks – due date in 2 days and status is ‘In progress’.

DueDate eq 'addDays(utcNow(),2,'yyyy-MM-dd')' and Status eq 'In progress'

If you’ve got more than 2 filters, all connected with and, you can add one after another.

filter1 and filter2 and filter3...

Multiple filters with OR

The same logic applies also to filters where it’s enough if one of the filters is true – the OR operator. For example, to get only items where status is ‘In progress’ or ‘Waiting for something’.

Status eq 'In progress' or Status eq 'Waiting for something'

Again, to combine more than 2 filters, all connected with or, you can add one after another.

filter1 or filter2 or filter3...

Combining AND and OR filters

The filters get more complicated once you start combining them together. If it’s a combination of AND and OR, you must get also their order right and define which filters belong together by adding brackets. For example, if you want a filter where due date is in 2 days and status is ‘In progress’ or ‘Waiting for something’, you’ll need brackets.

DueDate eq 'addDays(utcNow(),2,'yyyy-MM-dd')' and (Status eq 'In progress' or Status eq 'Waiting for something')

Check the status first, if it’s ‘In progress’ or ‘Waiting for something’. Take the result of the filter inside brackets, and use it together with the and filter on due date.

Power Automate multiple filter query

If you don’t add any brackets, the evaluation will go from left to right. It’d process the first two filters on due date and status ‘In progress’, and then take the result and process it with or on the ‘Waiting for something’ status. That’s not what you want. If you’re not sure, don’t worry about using too many brackets, e.g.

(filter1 or filter2) and (filter3 or filter4)

Just make sure to keep the filters that should be evaluated together in the same pair of brackets.

Summary

When using multiple filters in the Power Automate Filter Query, don’t overthink it. Just define the filters and connect them with and or or, depending on your needs. And if it’s a complex filter, add a few brackets to define which parts of the filter should be evaluated together.

And if the composed filter doesn’t work, try to check each filter separately. From my experience with complex filters it’s more often problem in one of the filters than in their combination.


Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

4 thoughts on “How to combine multiple filters in Get items Filter Query (Power Automate)”

  1. Jolin says:
    April 13, 2022 at 8:45 am

    Hi,

    could you help to have a loot at the filter Query as below, seems it always ignore the part filter (Progress ne ‘Completed’ or Progress ne ‘Blocked’) automatic, what’s up, appreciate your reply, thanks!

    DueDate eq ‘@{addDays(utcNow(),variables(‘varNumDays’),’MM-dd-yyyy’)}’ and (Progress ne ‘Completed’ or Progress ne ‘Blocked’)

    Reply
    1. Tom says:
      April 20, 2022 at 5:42 pm

      Hello Jolin,
      shouldn’t there be AND between the two Progress condition? As you have it now it’ll return ‘true’ because at least one of the conditions will be probably always true. For that part to be false the Progress would have to be Completed AND Blocked at the same time.

      Reply
  2. Lina Delgado says:
    October 26, 2022 at 6:20 pm

    I have an error in the Get Items sections with my concat formula : concat(concat(concat(‘ExpirationDate’,’ ge ‘, ””,outputs(‘Today_+_x_days’)),””,’ and ‘, concat(‘ExpirationDate’,’ lt ‘,””, outputs(‘x_Days_+_30’),””, ‘ExpirationDate’,’ ge ‘, ””,outputs(‘Today_+_x_days’)),””,’ and ‘, concat(‘ExpirationDate’,’ lt ‘,””, outputs(‘x_Days_+_60’),””)))

    its said : Bad Request

    Reply
    1. Tom says:
      November 6, 2022 at 8:03 pm

      Hello Lina,
      the Filter Query has a strict limitation on the format – type in column name value. You have to build simple filters (https://tomriha.com/what-to-enter-as-filter-query-in-the-power-automate-sp-actions/) and then connect them with the AND or OR.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Do you know what to do, but not how to do it?

Get The Ultimate Power Automate expressions cheat sheet and translate your thoughts into flows with ease!


There's also the HTTP requests to SharePoint cheat sheet to help you overcome limitations of some standard actions!

Do you struggle with the various expressions, conditions, filters, or HTTP requests available in Power Automate?

I send one email per week with a summary of the new solutions, designed to help even non IT people to automate some of their repetitive tasks.

All subscribers have also access to resources like a SharePoint Filter Query cheat sheet or Date expressions cheat sheet.

Zero spam, unsubscribe anytime.

  • How to find Excel rows missing in SharePoint list (Power Automate)March 29, 2023
  • Check whether user exists in AAD with Power AutomateMarch 26, 2023
  • How to highlight whole SharePoint row based on calculated dateMarch 22, 2023
  • Why is your Power Automate flow running so slow?March 19, 2023
  • How to add multiple links to the Power Automate approval taskMarch 15, 2023

Power Automate blogs worth visiting

Damien Bird
Dennis (Expiscornovus)
Paul Murana

©2023 Let's POWER Automate | Theme by SuperbThemes