Forum Discussion
Anonymous
6 years agoNot applicable
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...
- 6 years ago
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
MFelix
6 years agoSuper User
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
Anonymous
6 years agoNot applicable
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?