Filter Query, Order By on Excel Rows

(0) Share

Report

Posted on by gc2

Not able to use filter or Order by function on excel data, the error returned is

Syntax error at position X of the filter expression.

The filter condition is on a text column named "User Name", looking for value equals "Test Client 1",

the actual filter text used the flow is => User Name eq 'Test Client 1'

I had been searching online and came across this post from year 2016 which suggested filter and Order by are not supported for excel, however those properties can can be set in the flow UI.

https://powerusers.microsoft.com/t5/Flow-Ideas/Excel-ODATA-Filtering-obsolete-request/idi-p/11568

I can work around the filtering by using a Data Operation action, however I dont know how to perform the sort.

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

yashag2255 24,769 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @gc2

You can definitely use filtering and sorting in listing excel rows. Please refer to the screenshot below:
In the image on the left, I am querying to get all descriptions that have a value 3. In the right one, I am getting the rows sorted in the order of TaskID (this sorts in ascending order)

If you could share a screenshot of the error you are getting, We might be able to assist you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Was this reply helpful? Yes No
v-bacao-msft on at

Like (0)

Report
Copy link

Link copied!

Hi @gc2 ,

Currently both Filter Query and Order by are working properly.

However, there is a limit here. Please try to ensure that column name does not have spaces or special symbols, so as to ensure the normal use of this function.

For example, the table has two similar fields, User Name and UserName, and one of the field names contains a space.

When using these two fields in Filter Query and Order by to test, I found that the former will have the kind of error you mentioned, and the latter can work.

In addition, you could consider using Filter array to filter rows, which does not need to consider the particularity of the field name.

Best Regards,

Was this reply helpful? Yes No
gc2 16 on at

Like (2)

Report
Copy link

Link copied!

It turns out in Filter Query and Order fields, the column name connot have space character,
I have tried : User_Name eq 'Name 3', User_x0020_Name eq 'Name 3' both of which returns error.
As suggested, I can filter the data in a seperate Data Operation, but how do I apply sorting to the array?

Another reason I want to apply the filter in the List Rows action is hoping to get around the returning rows limit of 2048 which I read online, My excel file contains over 10,000 rows.

I found it frustating that FLOW still have these unreasonable limitation.

"status": 400,
"message": "Invalid filter clause: cannot find the 'User_x0020_Name' column.\r\nclientRequestId: de9e64fb-a652-4cbb-ab51-5cd23be14226",
"source": "excelonline-we.azconn-we.p.azurewebsites.net"

Was this reply helpful? Yes No
gc2 16 on at

Like (0)

Report
Copy link

Link copied!

This re-enforce the frustrations I mentioned previously.

Having convinced user to remove space characters in the column name because the Excel action is incapable to handle space, so 1 field condition UserName eq 'X Y Z' works, now when I try to the second filter condition, UserName eq 'X Y Z' and C1 eq '10', this failed with the following :

"status": 400,
"message": "Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.\r\nclientRequestId: f944045d-c2f1-4b49-8d28-6a458a8ea5f8",
"source": "excelonline-we.azconn-we.p.azurewebsites.net"

Applying indivduial condition is fine, but not more than 1 condition. I found reference post stating mulitple condition works although that was on Sharepoint list. Can anyone shed some light ? This should be a very simple task but it turns out taking days and not seeing the end.

Was this reply helpful? Yes No
v-bacao-msft on at

Like (0)

Report
Copy link

Link copied!

Hi @gc2 ,

It is true that we can't add multiple filters in Excel-Filter Query.

Currently, available feature is to configure a single condition in Filter Query to filter records, and use Order by to sort the filtered records. The premise is that the field name does not contain spaces or other special symbols.

For using multiple fields to filter records, please consider implementing this in Filter array-advanced mode.

Use List rows present in a table action to sort, use Filter array to filter, although the order of action changes, but the final result is the same.

In addition to the feature to configure multiple conditions in Filter Query, please consider submitting your idea in Flow ideas forum to allow MS Flow to support such functionality:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Best Regards,

Was this reply helpful? Yes No