Forum Discussion
Sorting a text/number column in matrix - not sorting correctly
It could be a combination of those account numbers being text and numbers. I see that all of the time when importing GL data from files.
I would create a sorting only column in Power Query. Just add a custom column and ensure it is a number. For example,
- Add this column - Call it "Account Sort" or something: =try Number.FromText([Account]) otherwise null
- Set the data type for that column to Whole Number
- Filter this column to show only NULL values. You should have none. If you do, that is another issue to resolve.
- Remove your NULL filter. That was just to see what was there.
- Load into the data model
- Once in the reporting section of Power BI, see image below
- Select the report tab
- Select your Account column
- Select the Column Tools tab and Sort By Column
- Select Account Sort or whatever you called this new field.
If that doesn't work, please post back with some sample data so we can play with it.
- Add this column - Call it "Account Sort" or something: =try Number.FromText([Account]) otherwise null
It could be a combination of those account numbers being text and numbers. I see that all of the time when importing GL data from files.
I would create a sorting only column in Power Query. Just add a custom column and ensure it is a number. For example,
- Add this column - Call it "Account Sort" or something: =try Number.FromText([Account]) otherwise null
- Set the data type for that column to Whole Number
- Filter this column to show only NULL values. You should have none. If you do, that is another issue to resolve.
- Remove your NULL filter. That was just to see what was there.
- Load into the data model
- Once in the reporting section of Power BI, see image below
- Select the report tab
- Select your Account column
- Select the Column Tools tab and Sort By Column
- Select Account Sort or whatever you called this new field.
If that doesn't work, please post back with some sample data so we can play with it.
Hi edhans
I actually allready had only the number part seperated - it was a wish from the customer to have the text part included in the field.
I tried sorting after that using your method and it worked
Thank you so much 🙂
Excellent Anonymous . The Sort By Column feature can be super handy sometimes.
I'm facing the same issue where I was trying to sort the Matrix header by descending order and it is not working. From the above example, I would like to sort the Matrix header in descending order. I tried to sort the Year using Sort by column and is not working. Also sorted the Year column in Power BI Query editor, it is showing in the descending order in Power BI Query editor and also in data tab. But when I pull in the fields in the visual on the Matrix header it is not sorting by descending. Any help please
You should create a new thread for this, and include more info. Ideally a PBIX file so we can see exactly what is going on. This thread has been marked as "solved" so no one not in this thread will see it to offer more help.