Not able to use filter or Order by function on excel data, the error returned is
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.
I can work around the filtering by using a Data Operation action, however I dont know how to perform the sort.
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!
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.
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 :
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:
Look out for new contribution recognition badges coming SOON!
Join us for the first ever Power Platform Online Conference!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!