How to sort months properly in Power BI
How do you sort your months chronologically? Or how can we put 'Other' at the end of the list without putting zzzz in front of it?
In this post I'll look at two methods for sorting your data correctly and in any custom sort order of your choosing in Power BI.
Method 1: Use 'Sort by Column' feature to sort Months of Year
Power BI Desktop (and Excel Data Model) have a feature that enable us to change the sort order of any column. The Sort by Column button works when you have another column in your data model that tells Power BI how to sort your data.
For example, in the table below, we have [Month] and [Month Number] columns. If we leave everything as default, the Month column will sort alphabetically, but by using the Sort by Column feature of Power BI, we can tell it to use the Month Number column to determine the order of the months.
In order for this to work, you must have:
- Two columns: the Display column and the Sort column
- A single value in the Sort column for every value in the Display column
- The Sort column must be independent of the Display column (it cannot use DAX conditions)
Let's look at some examples to see what that means.
It's okay to have the same value in the Sort column for different values in the Value column.
Here we have a Value column [Month Year] and we want to Sort by [Month Number]:
This works because every value of Jan-21 has the same value in the [Month Number] column. It's okay to have Month Number 1 for multiple values, they'll just get sorted by default within that range. For example if we sort Month Year by Month Number, and then sort the Month Year column Ascending, the result is:
Error: There can't be more than one value in 'Sort Column' for the same value in 'Value Column'.
We can't sort the 'Month' column by 'Month Year'. There can't be more than one value in 'Month Year' for the same value in 'Month'. Please choose a different column for sorting or update the data in 'Month Year'.
Error: 'A circular dependency was detected:
Failed to save modifications ot the server. Error returned: 'A circular dependency was detected: Table[Display Column], Table[DAX Sort], Table[Display Column].'
If you don't have a Sort column in your data model, you need to add it using Power Query M code, not DAX. Due to the Order of Operations in Power BI, we cannot use DAX as a Sort by Column in Power BI.
To overcome this problem, open Power Query, click New Column > Conditional Column and create your Sort column there. Then follow the steps in the Microsoft Docs link above to use the Sort by Column feature.
Method 2: Use M Code Zero Width Space Characthers to sort Power BI data
Power BI sorting sorts in ascending order with spaces and special characters first, then numbers, followed by text:
Your report users will notice if you start putting spaces at the beginning of your data, but we can use the Zero Width Space Character to add an invisible space that can't be seen by the naked eye, however Power BI will still see it and sort it before any other values without that space.
Zero Width Space Characters
In M code, the Zero Width Space Character can be written as:
Character.FromNumber(8203)
So to use this, we just need to add it as a prefix to any data. This will require some custom M coding, so we're going to use the interface to learn a few things. If you don't have the formula bar turned on, click the View tab in Power Query, tick the box next to 'Formula bar' to turn it on.
Add Prefix to Column
To add a prefix to any column, we just use the & operator in M. In order to see that in action, click the 'Add Column' tab > Format > Add Prefix
Type anything in the Prefix box:
Now check the formula:
= Table.AddColumn(Source, "Prefix", each "zero" & [Customer], type text)
Note the part that is underlined in the formula above. The prefix you typed in the box is in quotation marks, followed by the & symbol and the name of the column you had selected. If you look at your data preview, the prefix appears in front of the text in the column you had selected.
We can replace the "zero" with Character.FromNumber(8203) so that our formula now reads:
= Table.AddColumn(Source, "Prefix", each Character.FromNumber(8203) & [Customer], type text)
If you look at your data preview now, the new column should look the same as the original column, only there's an invisible space character there that we can't see.
This is cool, but doesn't help us with sorting when every value has this character.
Add Conditional Column
Now we're going to add a Conditional column, this will help give us the base structure we'll use to add our prefix. We want our VIP customers to appear first in the list, followed by our Standard, then Other. So to do this, we'll put two zero width space characters in front of the VIP customers, one zero width space character in front of the Standard, and none in front of the Other customers.
We're still using the built in interface to learn M code, so click the Add Column tab > Conditional Column.
In this area we'll just create the conditions and use the same 'Output' for each condition. This will give us the base formula, which we can then change to:
= Table.AddColumn(Source, "Customer Sort", each if [Category] = "VIP" then Character.FromNumber(8203) & Character.FromNumber(8203) & [Customer] else if [Category] = "Standard" then Character.FromNumber(8203) & [Customer] else [Customer])
Now you'll see if we sort by this new column, or use this new column in visuals, it will automatically sort the VIP customers first, then Standard, then Other:
