Natural sort order for text

Question

Is there a simple way to sort data in a natural order in PowerBI?

i.e. being able to sort a text column to be ordered as:

A1, A2, A10, A11, A12, A20, A21

instead of:

A1, A10, A11, A12, A2, A20, A21

Thanks.

sean · Answer

Yes duplicate the original column - split and create your Sort Column in the Query Editor - and then use that column to Sort By!&nbsp;But again don't forget you've applied this Sort or the ALL function will not give you the correct result!&nbsp;I almost started rebuilding a data model - because I couldn't figure out why I was getting wrong results...&nbsp;http://community.powerbi.com/t5/Desktop/ALL-Function-Mystery/m-p/107458/highlight/true#M45087

marcelbeug · Answer

It depends on the definition of "Natural"...&nbsp;My suggestion would be to add a column with the numeric part, sort on that column and then remove that column, as illustrated in this 30 sec video.

sean · Answer

MarcelBeugUnfortunately though this is not a substitute for Sort By Column in the Data View (you'll still have to do Sort By Column!)And then don't forget about this sort order column when you are using the ALL function!https://blog.crossjoin.co.uk/2015/12/15/power-bi-desktop-sort-by-column-and-dax-calculations-that-use-the-all-function/

datax · Answer

RL1&nbsp;As a workaround you can edit query. Split column by number of characters, use 1 left split , get a new numerical column and sort on that.&nbsp;Hope it helps!!

marcelbeug · Answer

Sean&nbsp;so apparently, one can't specialize in Power Query without sufficient DAX knowledge... :smileyembarrassed:If I understand&nbsp;corrrectly,&nbsp;the number column I created in my solution should be kept, so it can be used as sort column in the Data View?

Forum Discussion

Natural sort order for text

6 Replies

Related content

Table Sort , sort order , comparer

Sort Order issue

sorting months in order

PBI app audiences sorting/ordering

Field Parameters sort order