Skip to main content

Notifications

Power Automate - Building Flows
Suggested answer

Advanced Filter syntax

Posted on 23 Nov 2024 12:39:18 by

I am working on a Power Automate flow where I need to filter rows based on whether the values in a specific column match any value in a predefined array. Here's an example of what I am trying to achieve:

  • Column_Name: This column contains values like Value A, Value C, Value B, etc.
  • Array to Match: ["Value A", "Value B"]

I want to filter rows where Column_Name matches any value in the array ["Value A", "Value B"].

I tried using the eq operator, but I’m not sure how to make it work for multiple values in an array. Is there a way to use an OData filter query or an expression to achieve this in Power Automate?

  • Expiscornovus Profile Picture
    Expiscornovus 30,623 on 25 Nov 2024 at 10:08:01
    Advanced Filter syntax
    With the same approach Nived shared you could also use a substringof function to check multi-valued choice type of fields.
     
    1. The append to array variable action
    substringof('@{item()}',Column_Name)
     
    2. The OData filter
    join(variables('ODataQuery'), ' or ')

    Test result

    ​​​​​​​
  • Suggested answer
    Nived_Nambiar Profile Picture
    Nived_Nambiar 16,554 on 25 Nov 2024 at 02:46:42
    Advanced Filter syntax
    Hi,
     
    If you are planning to do via Odata filter query, contains approach is not supported for array as you require. But there is another way you can try around using or approach, this will create or conditions dynamically based on array elements.
     
    Assume you have a column name as FailedStation, I need to filter whether the column contains FS1 or FS2 in Sharepoint list. Refer how to do it as shown below:
     
    1. Use select action to convert the array values to filter query syntax as shown below.
     
    Expression - 
    concat('FailedStation eq ','''',item(),'''')
     
    2. Use join function to join all the elements of select action using or delimiter as shown below
     
     
    Expression-  join(body('Select'),' or ')
     
    3. Use the compose action's output in filter query field in get items action (if you are using sharepoint)
     
     
    So when you run - you will see the result like this
     
     
    So you can understand that how the array values can be converted to filter query.
     
    Hope it helps !
     
    Thanks & Regards,
    Nived N
     
    Mark it as solution if it resolves your query !
     
     
     
  • lbendlin Profile Picture
    lbendlin 7,821 on 24 Nov 2024 at 21:31:10
    Advanced Filter syntax
    Rows of what?  Where does the data originate? Did you load it into an array?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,246

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,884

Leaderboard