Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Power BI Number Ordering Problem (1, 10, 11, 12)

My data set has a value of Set=1, Set=2, etc.   I split the column by the delimiter "=", and now have one column that has "Set" and the other a Whole Number Value.  The whole number is now ordered ...
Need Help
Show and Tell
Tips and Tricks
  • TomMartens's avatar
    TomMartens
    9 years ago

    Hey,

     

    in your model, mark the column that contains "Set 1", "Set 10", "Set 11", ...

     

    In the Data View - Select the menu Modeling - (1) Mark the Column you want to sort - (2) from Sort by Column - (3) Select the column you want to use as SortIndex

     

     

    Hope this helps

    Regards

TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

Hey,

 

in your model, mark the column that contains "Set 1", "Set 10", "Set 11", ...

 

In the Data View - Select the menu Modeling - (1) Mark the Column you want to sort - (2) from Sort by Column - (3) Select the column you want to use as SortIndex

 

 

Hope this helps

Regards

  • oldwoman's avatar
    oldwoman
    Frequent Visitor
    2 years ago

    go to the data table view and select the column that has (1,10, 11...) then change the data type to whole number.  Then when you back to the report view you should be sorted by number not by text.

    • David_M_W's avatar
      David_M_W
      Frequent Visitor
      2 years ago

      Thanks. Kudos!

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    TomMartens

     

    Thanks.  This worked, but I have one question.

     

    For me to achieve the proper ordering, I sorted by the column that is formatted as a whole number.  

     

    In essence, I needed to create a column to refernce so that I could have the proper "Sort By".  Is this typical?

    • TomMartens's avatar
      TomMartens
      Icon for Super User rankSuper User
      9 years ago

      Hey,

       

      yes whenever you want or have to change the default ordering you have to create a reference column, eg a column called MonthName.

       

      Regards

  • Balakaye's avatar
    Balakaye
    Regular Visitor
    3 years ago
    yep this happened to me and I couldn't figure it out for the longest.
     
    For me, after I did the Split Column by delimiter (a space in my case) I didn't notice the numbers were being stored as text even though I changed the column's data type to Whole Number. This was because it retained the space with the numbers after the split. The solution that worked for me was to insert a step before I sorted them using the Trim function to remove the spaces.
     
    Tip: If numbers are being stored as text they will be displayed as left justified, whereas if they are being stored as numbers they will be italicized and right justified.
     
    Note: I originally encountered this problem in Power Query when using it in Excel. I tried replicating the problem in PowerBI Desktop and didn't run into this problem, it seemed smart enough to recognize that the values are indeed whole numbers and remove the spaces for me without the need for the extra Trim step. I doctored the below images to recreate the problem I had originally:
     
    Screenshot comparing numbers stored as text vs numbers stored as whole numbers