If a column in your table has duplicate values, Power BI won’t allow you to sort it by another column, unless both columns have the same granularity. To set a different column to sort by in Power BI, both columns need to be at the same level of granularity. For example, you need a column containing a number for each month, to sort a column of month names.
I encountered this issue when sorting by column with duplicates in Power BI while working on a Financial Reporting project. This short blog post explains how I solved the problem with a simple trick, let’s get started.
To demonstrate the problem and the solution, I prepared a sample financial statement layout in Excel with two columns, Hader and Header Sort. Pay attention to the blank lines on the Header column at Sort numbers 5, 12, 22. Power BI won’t allow you to sort the Header by the Sort column due to these blank values. However, we need to maintain the same sort order and the structure in Power BI as you see here.
Once you import the above table and add the Header to a Matrix visual, by default it’s sorted alphabetically as seen below without applying any sorting.
We need to sort the Header by the Header Sort column using the Sort By Column step in Power BI to get the Header sorted correctly as shown below by selecting the Header column and then clicking on Header Sort option.
But Power BI wouldn’t like that 
, it throws an error message because of the duplicate values in the Header, in this case the blanks in the Header having different sort numbers are causing the error . To fix this problem, let’s get inside Power Query and filter out the null values and load the Query.
Now, the sorting by the Header Sort column should work without errors because we removed the blanks ,there are no duplicates and both columns have the same granularity.
Now that the sorting is applied, we still need those blank values back in the table in order to keep the layout as expected.
Return to Power Query and delete the last step where we filtered out the null values, this will reset the table to it’s previous state including the blank values.
Load the query back and check the matrix now. It shows the layout including the blank blank lines as well 
This layout problem can be handled in various ways, but I demonstrated this approach to show how the default Sort by Column feature can be used differently.
To address this layout problem in a different way wihout the above sorting approach, you can include the Header Sort column before the Header in the matrix and shrink it to zero column width which makes the column invisible but ensure to turn of the Warp Text from Row and Column Headers.
I hope you found this article useful and learned something worthwhile. You can download the Excel and Power BI files below. I would appreciate it if you would share your comments and suggestions.
Thanks for reading the blog.
Cheers!
Fowmy Abdulmuttalib is a Microsoft-certified business intelligence professional with extensive experience in finance, accounting and data analytics. Currently, he is employed by McDonald’s Saudi Arabia as the Head of ERP and Business Intelligence. He is interested in everything data-related, he likes to share his passion and knowledge with the data community.
Thanks a mill, you did a great job. The info is very useful.
Thanks for reading the blog