Filter Query in List rows in a table Error

(0) Share

Report

Posted on by 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:

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (1)

Sundeep_Malik 6,484 on at

Like (0)

Report

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'

Was this reply helpful? Yes No
tareenmj 60 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (0)

Report

@tareenmj
Try without spaces.

Was this reply helpful? Yes No
Verified answer

v-liwei-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
tareenmj 60 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (1)

Report

@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-p/1624706

Was this reply helpful? Yes No
tareenmj 60 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
Sundeep_Malik 6,484 on at

Like (0)

Report

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

Was this reply helpful? Yes No