“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.
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.
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’)
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.
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
Hello Lina, 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.
the Filter Query has a strict limitation on the format – type in column name