Forum Discussion
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 ...
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
Super User
Super UserHey,
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
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