"Sort by column" with blank values throws error: We can't sort ... There can't be more than one ...

[mike_honey]

Ran into this weird issue while modeling in Power BI Desktop - it seems you now cannot set a Sort by column (i.e. on the Model ribbon) if the column you try to choose contains blank values.  An example of the error thrown is:

We can't sort 'Category' by 'Category Sort'. There can't be more than one value in 'Category Sort' for the same value in 'Category'. Please choose a different column for sorting or update the data in 'Category Sort'

I'm sure this wasn't a restriction previously - I have many models where it is happily sorting by a column that contains blank values.  If I edit those models and remove the Sort by column (select the 'Category' column) then try to re-select the previously used column as the Sort by column, it throws the error above. NB there's no Undo at that point, so your only option is to close without saving.

I've built a simple repro of this issue. 'Table' contains a row with blanks for 'Category' and 'Category Sort'. If you try to set the Sort by column for 'Category' to be 'Category Sort', it throws the error above.

'Table (with no blank sort keys)' is the same data, but with the blank 'Category Sort' values changed to a value of 0. For this table, the Sort by column for 'Category' can be set to 'Category Sort'.
https://1drv.ms/u/s!AmLFDsG7h6JPiIx5t0uKQYrSRNz-AA?e=gJ2OIU

Am I going crazy?

[mike_honey]

PS: if you open my bug repro PBIX (link in the first post) and edit the query "Table (with no blank sort keys)" and delete the last step "Replace Values", then the data in the table will have blank values in the Sort by column, but the 'Category' values will still be sorted by the 'Category Sort' column. Power BI Desktop is fine to refresh the data into this state, which is how the validation logic used to work.   

[Ritaf1983]

I saw your file and I apologize, but I don't understand what you're trying to say..

If your intention is to say that you can return an 'empty' value after setting the sort order without affecting it, then okay, I’ve seen that it seems to work. I don’t have any proof on the matter, but intuitively, I think there’s a high chance that something might go wrong during one of the refreshes or changes. Setting a sort order based on such a column won't succeed (at least as of now).

[mike_honey]

I've created an Idea to get the Power BI Desktop validation logic reverted to how it used to work.  If anyone agrees, please vote on it.

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=b7bf1b69-2b59-ef11-b4ac-6045bdbb1b51

[Ritaf1983]

Hi @mike_honey 
Indeed, it is not possible to conditionally sort based on an empty column. To address this, you need to decide whether you want the blanks to appear at the beginning or the end, and then add a helper column with the desired logic, assigning a corresponding number instead of leaving it blank. I am attaching a link to a demonstration video.
https://www.youtube.com/watch?v=hdEbtlPVWoM

If this post helped, please consider Accepting it as the solution to help the other members find it more quickly

[mike_honey]

Hi @Ritaf1983 , thanks for the super-quick response.

I guess my points are:
1. It used to be possible to sort by a column that contains blank values - I have many existing models that feature that scenario.
2. AFAIK the documentation makes no mention of that requirement, eg 
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto...

[Ritaf1983]

Hi @mike_honey 
I've been using Power BI since 2017, and I've never encountered a case where this was possible. Also, note that the video I shared with you is quite old (5 years). I'd love to see an example where it worked and be pleasantly surprised!

Also, null/white space is not a value like 0, which has a clear numerical order. You simply can't base sorting on what 'isn't there'; sorting is done according to the order of what exists.

Regarding the Microsoft document, feel free to leave feedback.

If this post helped, please consider Accepting it as the solution to help the other members find it more quickly

[mike_honey]

The scenario I am talking about is using the Sort by column feature e.g. in the Modeling ribbon of Power BI Desktop.
https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column?tabs=powerbi-deskto...

Your video shows sorting in Power Query, which is not relevant.

[Ritaf1983]

@mike_honey 
That's correct, the sorting column needs to be created in Power Query. If you create it using a calculated column in DAX, you won't be able to base the sort on it due to another issue - a circular dependency. Again, I'd be happy to see an example proving me wrong, but for the past 7 years, it has worked as I've described. To sort by a particular column, it must contain sortable values in all its cells.