How to combine multiple filters in Get items Filter Query (Power Automate)
Posted On September 12, 2021
“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.
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.
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.