cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pennyland
Advocate II
Advocate II

In power automate: How can i filter data from excel using 2 different columns at the same time?

I'm using a 'list rows present in a table' in my flow. Next I want to return only specific records, but i need to filter on 2 separate columns.

 

I've tried using a filter array and the odata filter but it's either returning incorrect records, or i get that "Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported."

 

I've tried in the odata filter query and using a filter array... 

@And(equals(item()?['Lev2Approval'], 'PREAPPROVED'), equals(item()?['Lev3Approval'], null))

 

This fails regardless of which one i use.

 

One of the conditions is to find where the values in that column are null.

 

Thanks so much for your help!  

10 REPLIES 10
ScottShearer
Super User III
Super User III

@pennyland 

I don't see anything obviously wrong with your expression.  Can you post some sample data so I can try to replicate your issue and solve the problem?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Scott

I figured out that if I use the following, the flow completes successfully. But i'm still getting the wrong records back in the output.

 

@And(equals(item()?['Lev2Approval'], 'PREAPPROVED'), empty(item()?['Lev3Approval']))

 

Hi @pennyland ,

 

Could you provide more explanation about "still getting the wrong records back in the output"?

 

Is the condition you configured in the Filter array not working or something else? Please check the output part of Filter array-Body instead of input.

 

Please provide more details so that we could understand it better.

 

Besides, we could not fill two conditions in Filter Query to filter records, so we could only use Filter array to filter records if we want to apply two filters.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Here is a copy of the flow i'm using. It completes with a status of success.

msflow_error_for_reminders.png

 

Here are the columns I'm referencing in my flow(excel):

 

msflow_headers.png

 

When i use the following in my 'filter array', 

@And(equals(item()?['Lev2Approval'], 'PREAPPROVED'), empty(item()?['Lev3Approval']))

I get data in the 'Lev3Approval' column other than just empty cells.

And I get data with empty cells back under 'Lev2Approval' column.

 

Why is the query NOT returning ONLY those records that have the word 'PREAPPROVED' in the Lev2Approval column and empty in the Lev3Approval column?  

In case it's pertinent, yes the data for those columns in the excel is in ALL CAPS.

 

 

Hi @pennyland ,

 

There is no problem with the expression you use, you can filter the records you expect.

 

Do you mean that the output of the Filter array contains unexpected records?

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yes the output of the filter contains records that don't match the filter.

Circling back on this question as there haven't been any further responses.  Is anyone else having issues trying to get 2 different fields to filter correctly using odata or filter query?

Radoslav_Yotov
New Member

I need your support on this topic: how do we use the filter array in the creation of the html/csv table? If I use the value from the "List rows present in a table" action, it returns all the values and the filter array is not at all taken into account. Thanks!

Radoslav_Yotov
New Member

I need your support on this topic: how do we use the filter array in the creation of the html/csv table? If I use the value from the "List rows present in a table" action, it returns all the values and the filter array is not at all taken into account. Thanks!Screen Shot 2021-03-13 at 10.13.03 AM.png

@Radoslav_Yotov it looks to me as if you have used value (the entire array) for both, the Filter array From and the Create CSV table From arguments. The From argument for the table should be the filtered array, look for body as the output from that Filter array action.

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (55,510)