Forum Discussion
Sorting a text/number column in matrix - not sorting correctly
Hi guys
I have a matrix visualisation with a text field that includes both numbers and text. I have tried soring it the 'normal' way where i go to the "..." in the visual and choose "Sort by" and then select my field - but the values dosent sort correctly all the way.
I know of the leading zero solution, but that is not the problem here unfortunately.
If it matters then the field im trying to sort is the second value in the 'rows'.
I really hope you can help me, and thank you in advance 🙂
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
9 Replies
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.
- Add this column - Call it "Account Sort" or something: =try Number.FromText([Account]) otherwise null
Hi Anonymous ,
Based on my tests I was abble to sort everything correctly however my data is just based on your small sample.
Can you share a sample file?
Regards,
MFelix
Hi MFelix
Thank you for your reply
Im unfortunately not allowed to share the file, but is there any other information i can give you?
If you are using matrix visual, it will not sort on your desired column.
A workaround is sorting it in powerquery then applying it.
How this helps.
Hi mussaenda
Thank you for the reply
I tried to sort the column in the powerquery, but it is still not sorting in the matrix
