Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
  • edhans's avatar
    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, 

    1. Add this column - Call it "Account Sort" or something: =try Number.FromText([Account]) otherwise null
      1. Set the data type for that column to Whole Number
    2. Filter this column to show only NULL values. You should have none. If you do, that is another issue to resolve.
    3. Remove your NULL filter. That was just to see what was there.
    4. Load into the data model
    5. Once in the reporting section of Power BI, see image below
      1. Select the report tab
      2. Select your Account column
      3. Select the Column Tools tab and Sort By Column
      4. 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.