Sorting question

[LouisT]

Hey everyone, just starting to work in Power BI. I've got a Matrix built the way i want, however the column headers are with a strange naming convention for the purpose of sorting them. ex: 01_Blue, 02_Red, 03_Yellow, etc.,
Anyhow, what i'd like is to keep the order based on the numerical value, but have the header ONLY show the colour. Is that doable?

Thanks in advance everyone.
LouisT

 

[smozgur]

The prefix seems to be 3 characters unless there are more than 99 colors. In this case the following might help:

let
    Source = #table({"01_Blue", "02_Red", "03_Yellow"}, {{1, 2, 3},{2, 3, 4},{3, 4, 5},{4, 5, 6}}),
    RenameColumns = Table.RenameColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.RemoveRange(_, 0, 3)} ))
in
    RenameColumns

If we need to find the underscore position and rename the columns dynamically:

let
    Source = #table({"01_Blue", "02_Red", "03_Yellow"}, {{1, 2, 3},{2, 3, 4},{3, 4, 5},{4, 5, 6}}),
    RenameColumns = Table.RenameColumns(Source, List.Transform(Table.ColumnNames(Source), each {_, Text.RemoveRange(_, 0, Text.PositionOf(_, "_") + 1)} ))
in
    RenameColumns

Edit: You mentioned ordering—if you also need to sort these columns at the end of the table:

let
    Source = #table({"Another Header", "01_Blue", "03_Yellow", "02_Red"}, {{"A", 1, 2, 3},{"B", 2, 3, 4},{"C", 3, 4, 5},{"D", 4, 5, 6}}),
    ExcludedColumns = {"Another Header"},
    ReOrderColumns = Table.ReorderColumns(Source, ExcludedColumns & List.RemoveItems(List.Sort(Table.ColumnNames(Source)),  ExcludedColumns)),
    RenameColumns = Table.RenameColumns(ReOrderColumns, List.Transform(Table.ColumnNames(ReOrderColumns), each {_, Text.RemoveRange(_, 0, Text.PositionOf(_, "_") + 1)} ))
in
    RenameColumns