Forum Discussion

WILLIH's avatar
WILLIH
Helper I
5 years ago

Id sequence

How do I get my id's to sort in order?

 

          

Need Help

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi WILLIH ,

     

    You need to convert the values into number, on the query editor if you click 123/ABC button you can select number on format.

    • WILLIH's avatar
      WILLIH
      Helper I
      5 years ago

      Hi MFelix,

      I did try changing the format of the data to a number but it still does not let me sort it. You can't see it from the pictures but some of the id's also have alpha in them like "D0017" or "EB255". Would that make a difference because the data is still being recognized as text?

       

      Thanks,

       

      WILLIH

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi WILLIH  if you alphanumeric codes you cannot convert to number,

         

        Believe the best option is to add a new column with some additional zeros to the left on alll the number so you can then sort the numbers. 

         

        Not on computer now but will send out the code tomorrow. 

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi WILLIH ,

     

    Try the following code to add a custom column that you can then sort the other column by this one:

     

    Power Query

    try if  Value.Is (Number.From([ID]), type number) then Text.PadStart([ID], 10 , "0" ) else [ID] otherwise [ID]

    Dax

    Column = IF(IFERROR(VALUE('Table'[ID]);BLANK()) = BLANK();'Table'[ID];REPT("0";10 - LEN('Table'[ID])) & 'Table'[ID])