cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gc2
Level: Powered On

Filter Query, Order By on Excel Rows

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.

 

5 REPLIES 5
Dual Super User
Dual Super User

Re: Filter Query, Order By on Excel Rows

Hi @gc2 

 

You can definitely use filtering and sorting in listing excel rows. Please refer to the screenshot below: 

esss.png

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!

Community Support Team
Community Support Team

Re: Filter Query, Order By on Excel Rows

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.

30.PNG31.PNG

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

32.PNG

 

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.
Highlighted
gc2
Level: Powered On

Re: Filter Query, Order By on Excel Rows

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.
 
image.png
"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"
gc2
Level: Powered On

Re: Filter Query, Order By on Excel Rows

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.
 
Community Support Team
Community Support Team

Re: Filter Query, Order By on Excel Rows

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.

11.PNG

 

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,

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.

Helpful resources

Announcements
firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Users online (4,325)