Forum Discussion

RL1's avatar
RL1
Frequent Visitor
9 years ago

Natural sort order for text

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...
Need Help
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

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

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's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

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's avatar
    Dog
    Icon for Responsive Resident rankResponsive Resident
    9 years ago

    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" )
    )