“I know I can use the ‘Filter array’ action in Power Automate to do this, but how do I set it up to use multiple conditions?”
When you need to filter data in Power Automate, you’ve got two options. Filter the data right away in some ‘Get…’ action, e.g. ‘Get items’, or filter them later with the ‘Filter array’ action. It’s always better to filter them right away, but sometimes you don’t have such an option. It can be an action that doesn’t include a filter, e.g. to get Planner buckets, or an unsupported data type, e.g. the SharePoint Multiple lines of text column. In such situations you must get all the data, and filter it later. But the interface of the ‘Filter array’ action has fields only for a single condition.
Is there a way to use more than just one condition in the action?
Use the advanced mode
The limitation on a single filter is only in the user interface, not in the action itself. If you switch to the advanced mode, you’ll avoid any limitation on the number of conditions. It has a downside though, in the advanced mode you lose access to any dynamic content. You must define the conditions manually.
Luckily, you can use the same workaround as with trigger conditions. Add a second ‘Filter array’ action just to help you create the conditions. Define the condition using the dynamic content and expressions, and switch to the advanced mode. You’ll get the condition to use in the original ‘Filter array’ action.
All that’s left is to remove the @ from the conditions and define their relation, if it’s AND or OR. Use @and(…) or @or(…) to group the conditions.
@and(condition1, condition2, condition3,...)
@or(condition1, condition2, condition3,...)For example:
@and(equals(item()?['DateTime'], utcNow('yyyy-MM-dd')),equals(item()?['MultipleLoT'], null))This way you can add as many conditions as you need into a single ‘Filter array’ action.
Note: the @ is always only once at the beginning, no matter how many conditions you use.
Summary
When you need multiple conditions in the Power Automate ‘Filter array’ action, you must use the advanced mode. It might look complicated at first, but you can use another to ‘Filter array’ to define the conditions in the user interface. Add the relation between the condition and that’s it. Get back the dynamic content after the ‘Filter array’ (if needed) and use the filtered data in your flow.
I would like to see and example that has both an “or” and and “and” condition combined as this does not see to work. Would you provide this for me?
Hello Steve,
start from the lowest level and connect them together, e.g.
1 or 2
@or(1, 2)
(1 or 2) and (2 or 3)
@and(or(1,2),or(2,3))
((1 or 2) and (2 or 3)) or ((4 and 5) or (5 and 6))
@or(and(or(1,2),or(2,3)),or(and(4,5),and(5,6)))
Hello,
I have two filter arrays but I’m receiving errors when I try to combine them. Can you help?
@contains(item()?[‘importance’], ‘high’)
@not(equals(item()?[‘completedDateTime/dateTime’], null))
Hello S. Sprague,
remove the @, create the condition and add the @ back, e.g.
@and(contains(item()?[‘importance’], ‘high’),not(equals(item()?[‘completedDateTime/dateTime’], null)))
@or(contains(item()?[‘importance’], ‘high’),not(equals(item()?[‘completedDateTime/dateTime’], null)))
I’ve struggled with this Filter Array condition for almost three days now. Is it apparent what I’m doing wrong here?
@and(equals(item()?[‘Okay_x0020_to_x0020_forward’]?[‘Value’], ‘Yes’),equals(item()?[‘ApprovalNotificationSent’]?[‘Value’], ‘No’))
Hello Ken,
it’s hard to tell without knowing the column types, but I’d try 0/1 or expressions true/false instead of the Yes/No values to see what happens.
I struggle with filter end result. I created a Filter that finds specific file on one Sharepoint site and that works.
However when I try to add Sharepoint Copy for each it breaks down.
Hello Emir,
that’s a bit too generic problem description to give you any advice.
can someone help to see if anything wrong with this code?
@and(
equals(item()?[‘ProductName0’]?[‘Value’],triggerBody()?[‘ProductName0’]?[‘Value’],
equals(item()?[‘Title’],triggerBody()?[‘Title’],
equals(item()?[‘Description’],triggerBody()?[‘Description’]
)
Hello Cara,
you’re missing closing brackets for each of the equals(…) expressions, none of them is closed:
@and(
equals(item()?[‘ProductName0’]?[‘Value’],triggerBody()?[‘ProductName0’]?[‘Value’]),
equals(item()?[‘Title’],triggerBody()?[‘Title’]),
equals(item()?[‘Description’],triggerBody()?[‘Description’])
)
Hi, I followed the instructions and this is my statement, but Power automate does not like it: @and(not(contains(item()?[‘Status’], string(‘Complete’)),less(item()?[‘Expected Completion’], item()?[‘CurrentDate’]),equals(item()?[‘Report Sent’], string(‘No’)))
Hello Toinette,
you’re missing a bracket: https://tomriha.com/how-to-fix-an-invalid-expression-in-power-automate/
Hi , I’m very disappointed with me im trying to complete my task and using the filter array conditions according to your steps but still I face a error msg ‘fix invalid expression for the input parameter of filter array ‘.
My filter query is …
@and(equals(item()?[‘Month_x0020_Num’],int(‘varTimesheetMonth’)))
equals(item()?[‘Approver/Email’],body(‘Select_HiringManager’))
equals(item()?[‘Approve_x0020_Status’],’CG Approved’))
Last ‘CG Approved’ is hard coded not taking from dynamic content.
Please help me
Hello Kaushal,
I wrote a separate article for debugging expressions: https://tomriha.com/how-to-fix-an-invalid-expression-in-power-automate/. In your case you’re missing commas and have a mess in the brackets.
Thank you again Tom, your work is invaluable! You continue to save me hours of work and better test results. Your efforts to help are greatly appreciated!
I would appreciate help with this, I want to filter for all of yesterday’s incomplete tasks:
@and(
equals(
formatDateTime(item()?[‘DueDateTime’],’yyyy-MM-dd’),
formatDateTime(addDays(utcNow(),-1),’yyyy-MM-dd’),
equals
(item()?[‘percentComplete’],0)
)
Hello,
Like the others on here I am struggling with a Filter Array. Essentially, I have data pulled from SharePoint Lists using Power Automate, I want to use multiple conditions for a filter arrays that basically allow 2 ‘or’ conditions with an ‘and’ applied to both.
Can you please help me stop wasting days of my life!
Thanks,
Scott
Hi, this is a great article. For a more complex case where you have a nested array, and you want to filter by a parameter within the nested array, is it possible to use a filter query? For example I’m trying to only include data when a specific field in the nested array is empty.