Forum Discussion

Nash2Bos's avatar
Nash2Bos
Icon for Helper I rankHelper I
1 year ago
Solved

How to re-arrange column values in a matrix table

Hi all, 

I have a table with a column, Environment with values (Dev, TE1, prod)

When i put that column into a matrix table is comes up as Dev, Prod, TE1. How can i arrange it so TE1 is in the middle and not alphabetical. 

Thank you 

Need Help
Tips and Tricks
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello Nash2Bos,

     

    Can you please try to add a new column to your table with custom sort order values:

    EnvironmentSort = 
SWITCH(
    'Table'[Environment],
    "Dev", 1,
    "TE1", 2,
    "Prod", 3,
    BLANK()
)

     

    Power BI uses the Sort by Column feature to override the default alphabetical sorting of text fields.

     

    By creating a numeric sort column (EnvironmentSort), you can define any custom order you want.

     

    Hope this helps.

     

  • hnguy71's avatar
    hnguy71
    1 year ago

    Hi Nash2Bos 

    An  easy way is if you can, you'd create a unique identifier number and then sort the column by it.  The index column can be done inside powerquery and then sorted in Power BI. For example,

    Then, after you've applied your changes, click sort by column:

     

7 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    Hello Nash2Bos,

     

    Can you please try to add a new column to your table with custom sort order values:

    EnvironmentSort = 
SWITCH(
    'Table'[Environment],
    "Dev", 1,
    "TE1", 2,
    "Prod", 3,
    BLANK()
)

     

    Power BI uses the Sort by Column feature to override the default alphabetical sorting of text fields.

     

    By creating a numeric sort column (EnvironmentSort), you can define any custom order you want.

     

    Hope this helps.

     

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Icon for Administrator rankAdministrator
      1 year ago

      This case would not work, it would create a dependency

    • Nash2Bos's avatar
      Nash2Bos
      Icon for Helper I rankHelper I
      1 year ago

      But when i put it in the table Prod is still in the middle

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    1 year ago

    Hi Nash2Bos 

    An  easy way is if you can, you'd create a unique identifier number and then sort the column by it.  The index column can be done inside powerquery and then sorted in Power BI. For example,

    Then, after you've applied your changes, click sort by column:

     

    • Nash2Bos's avatar
      Nash2Bos
      Icon for Helper I rankHelper I
      1 year ago

      thank you but when i try this, Prod is still in the middle of the matrix table

      • hnguy71's avatar
        hnguy71
        Icon for Super User rankSuper User
        1 year ago

        Hi Nash2Bos ,
        You may need to make sure that the visual sort is set to that column as well. 

         

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    1 year ago

    Hello, you must create an order column to be able to adjust it to your needs. You'd want to do this directly in Power Query with a conditional column and returning a numeric value.

    Then in table view, you select the column to sort and the column tools are enabled. Select "Sort by" by referencing the created column

    That will solve your problem