SortByColumns and Filter Combo for Dropdown Sourced from Excel

(1) Share

Report

Posted on by ComicSansrival

166

Hi,

I would like to ask for your assistance in figuring out ways to sort the dropdown value in ascending order.

I am trying to use excel file as data source of my dropdown because it will be easier for me to maintain one rather than creating a new MS list for this.

I have this formula: Filter(Client_numbers,Client = DataCardValue33.Selected.Result)

Because I want to trim the selection of client number based on the selected client name

Now, after the formula I want to sort "Client number" in ascending order:

SortByColumns((Filter(Client_numbers,Client = DataCardValue33.Selected.Result)),"Client Number",SortOrder.Ascending)

But using this formula returns an error:

Error. (Error when trying to retrieve data from the network: Syntax error at position 11 in 'Client Number asc'. inner exception: Syntax error at position 11 in 'Client Number asc'. clientRequest ld: 1e6656ac-f718-48b4-b24f-7d116aca599d)

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

WarrenBelz 153,049 Most Valuable Professional on at

Like (1)

Report

Hi ComicSansrival,

You might want to rethink the choice of data sources as Excel is a terrible option compared with Lists (SharePoint) and really should only be used as a last resort if Lists are not available to you. There is nothing wrong with the code you have posted assuming all of your field names are valid - Excel at times simply does not cope with the more complex filters. Also, you have very limited ability to "maintain" an Excel table used in Power Apps from outside Power Apps.

I suggest you try a List with the same fields - it should work fine.

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee

Was this reply helpful? Yes No
timl 36,383 Super User 2025 Season 2 on at

Like (2)

Report
Hi ComicSansrival,

This will be due to a limitation of the Excel connector in filtering/sorting by different columns.

If you use a With block to extract the Filter element, that should hopefully resolve the error.

With({data: Filter(Client_numbers,Client = DataCardValue33.Selected.Result)}, SortByColumns(data,"Client Number",SortOrder.Ascending) )

Was this reply helpful? Yes No
WarrenBelz 153,049 Most Valuable Professional on at

Like (0)

Report

You can go with timl here as that will work providing the filter returns less than your Data Row Limit, but please note my comment on Excel in general

Was this reply helpful? Yes No