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

[Anonymous]

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.

[jacobsmith1031]

This video helped! I was looking for ages!

Power BI Week Number | Sort Week Number in the right Order | Convert Dates into Week Number in PBI

https://youtu.be/FjvjL09dvio?si=Gv2ruE9dqbAyeiMM

[TomMartens · Accepted solution]

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]

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]

Thanks. Kudos!

[Balakaye]

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]

@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]

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

[Anonymous]

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.