Natural sort order for text

[RL1]

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.

[dataX]

@RL1 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.

Hope it helps!!

[MarcelBeug]

It depends on the definition of "Natural"...

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]

@MarcelBeug

Unfortunately 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-us...

[MarcelBeug]

@Sean so apparently, one can't specialize in Power Query without sufficient DAX knowledge...

If I understand corrrectly, the number column I created in my solution should be kept, so it can be used as sort column in the Data View?

[Sean]

Yes duplicate the original column - split and create your Sort Column in the Query Editor - and then use that column to Sort By!

But again don't forget you've applied this Sort or the ALL function will not give you the correct result!

I almost started rebuilding a data model - because I couldn't figure out why I was getting wrong results...

http://community.powerbi.com/t5/Desktop/ALL-Function-Mystery/m-p/107458/highlight/true#M45087

[Dog]

Hi, 

it's a bit long winded but might overcome the ALL scenario 

could you consider creating a calculated column from the split columns that pads the numerical part into a new column 

so 

A1 becomes two columns A and 1 but extra new column created has the A and then a "padded with zeros" (format to 10 perhaps) 

A1     =     A0000000001

A2     =     A0000000002

A11   =     A0000000011

then sort by the new column?

I'm sure there is a more graceful way of this but you could probably do this in one calculated column. 

=
VAR TextToSearch = Nominals[Management Reporting Code]
VAR At1 =
IFERROR(FIND ( "0", TextToSearch ), BLANK())
VAR At2 =
IFERROR(FIND ( "1", TextToSearch ), BLANK())
VAR At3 =
IFERROR(FIND ( "2", TextToSearch ), BLANK())
VAR At4 =
IFERROR(FIND ( "3", TextToSearch ), BLANK())
VAR At5 =
IFERROR(FIND ( "4", TextToSearch ), BLANK())
VAR At6 =
IFERROR(FIND ( "5", TextToSearch ), BLANK())
VAR At7 =
IFERROR(FIND ( "6", TextToSearch ), BLANK())
VAR At8 =
IFERROR(FIND ( "7", TextToSearch ), BLANK())
VAR At9 =
IFERROR(FIND ( "8", TextToSearch ), BLANK())
VAR At10 =
IFERROR(FIND ( "9", TextToSearch ), BLANK())
VAR ActualPos =
SWITCH (
TRUE (),
At1 <> BLANK (), At1,
At2 <> BLANK (), At2,
At3 <> BLANK (), At3,
At4 <> BLANK (), At4,
At5 <> BLANK (), At5,
At6 <> BLANK (), At6,
At7 <> BLANK (), At7,
At8 <> BLANK (), At8,
At9 <> BLANK (), At9,
At10 <> BLANK (), At10,
BLANK ()
)

var Number =if(ActualPos <> blank(), MID ( TextToSearch, ActualPos, LEN ( TextToSearch ) - (ActualPos - 1) ))
RETURN
IF (
ISBLANK ( ActualPos ),
BLANK (),
LEFT ( TextToSearch, ActualPos -1 )
& FORMAT (VALUE(Number),
"00000" )
)