Filter Query in List rows in a table Error

[tareenmj]

I am trying to filter my Excel document for certain values using Power Automate and then emailing the resultant rows. 

Currently, my flow looks like: 

I am basically filtering on a column in my data set called 'ERP Site Location Code' for a certain value and receiving all associated rows. However, I get the following error: ')' or ',' expected at position 7 in 'eq(ERP Site Location Code, "ZCOW1")'.

I don't understand what I'm doing wrong. I also did verify my excel file contains the correct column name: 

[Sundeep_Malik]

Hey @tareenmj 

Not sure that syntax works or not.

Try writing the below syntax:

Column name eq 'Value'

So for you:

ERP Site Location Code eq 'ZCOW1'

[tareenmj]

Thank you for the help, I tried that and seem to be receiving the same error and I think it is because of the fact that my Column name has spaces, I even tried doing: eq(ERP_x0020_Site_x0020_Location_x0020_Code, 'ZCOW1') but this results in another error saying: Invalid filter clause: cannot find the 'ERP_x0020_Site_x0020_Location_x0020_Code' column.

I think this has to do with spacing but I'm unsure on how to reference a space in Power Automate

[Sundeep_Malik]

@tareenmj 

Try without spaces.

[v-liwei-msft · Accepted solution]

Hi  @tareenmj 

This is a known limitation, the Filter Query in List rows present in a table action does not support the use of fields with spaces in the field name as filter parameters.

I suggest you to use the Filter array feature.

Best Regards,

Levi

[tareenmj]

Thanks for the input, would this result in similar time performances? A) If I receive all results, and then do a filter array, and B) Changing the column names to perform a Filter Query? 

[Sundeep_Malik]

@tareenmj 

That would definately affect the performance.

Renaming the excel columns will be the best way.

So let's say: you have 100 rows in your excel with filter query you are just fetching the required rows. 

But with Filter array you will be fetching entire rows and then processing. 

Just check out the below batch process that might help to reduce your flow times for excel processing: 

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

[tareenmj]

Thanks Sundeep! Your answers and recommendation was very helpful. I did try removing spaces from my Excel file and i was able to receive data within 3 seconds (my excel file has a count of 250k rows). I did have a feeling that fetching all rows would drastically reduce performance and obviously with an excel file with that many rows, I would have to do some batch processing. I'm just going with the removing spaces approach but I don't like how power automate is so buggy.

[Sundeep_Malik]

@tareenmj

Yup I know, faced one situation today. I had made a flow yesterday. Today it gave error in 1 variable. Copied the same flow line by line, expression by expression and made another flow and its working fine. And the first flow is still giving error.