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 correctly 1 thru 20.

 

That said, my preference for a user experience would for it to say Set 1, Set 2, Set 3.  Is there a way to either concatenate these two columns and maintain the proper order of 1, 2, 3, 4 - 20?

 

When I concatenate it it reorders as 1, 10, 11 because it no longer sees it as a whole number, similar to the image below when it's in original form.

 

 

Thanks.

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

8 Replies

  • 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.

    • 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
       
  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    You can change how a column sorts by going to the Modeling tab. So, for a given column, you set it to sort by another column. In your case, select the "Set 1, Set 2, Set 3, etc." column, go to Sort by Column and select the "Number" column.