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

[pennyland]

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!  

[ScottShearer]

@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?

[pennyland]

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']))

[v-bacao-msft]

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,

[pennyland]

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

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

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.

[v-bacao-msft]

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,

[pennyland]

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

[pennyland]

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?

[Anonymous]

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!

[Anonymous]

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!

[Anonymous]

@Anonymous 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.

[takolota]

Hello Pennyland,

On the filter formula that wasn’t working, did you try making it similar to how it would appear in the basic editor?

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

[takolota]

Also,

There are at least 2 ways to achieve multiple filter conditions for Excel since it doesn’t really support multiple filter conditions in a single action.

The second method I list is also 100x faster & more efficient for larger datasets.

First, you could pull all the data from the entire table and then use the output in a Filter Array.

To get the data from Filter Array, either use a Parse JSON action afterwards to get the data in dynamic content, or put the action in an Apply to Each with the Filter Array body as the loop value and use expressions like item()?[‘ColumnName’] to get that column/field value.

Second, besides using Filter Array after pulling all the data in the table, you could use a different Excel List rows for each filter condition and combine them after like:

Get rows 1 - Filter Status eq ‘Complete’

Get rows 2 - Filter Status eq ‘In-Progress’

Compose (To simulate an OR statement) - Expression Union(Get rows 1, Get rows 2)

Get rows 1 - Filter Status eq ‘Complete’

Get rows 2 - Filter Department eq ‘Sales’

Compose (To simulate an AND statement) - Expression Intersection(Get rows 1, Get rows 2)

Then you can use the same Parse JSON or item()?[‘ColumnName’] method on that compose.

I used 1/3 the API actions and had a 100x speed improvement using this second method. It’s also much easier to work with if you have several hundred thousand rows to work with. The 1st method would take forever to load that many and then you’d have to still filter every 100k batch & combine them.

[rych]

Regarding your 2nd method, would a Union function work in the same or better manner? Considering it can remove duplicate entries?