Search code examples
excelpowerbidaxm

How to sort dynamic amount of columns in M

PowerQuery/Excel:

I got table with dynamic amount of columns named Level 1, Level 2, Level 3... etc and i need to apply Table.Sort(x,Order.Ascending) to all of them in same order, as they are.

I tried to create list from Table.ColumnNames and insert it directly into Table.Sort column name parameter, but it doesnt work. I also tried to create function, that would loop thru all columns names and apply sorting to each, but my knowledge of functions in DAX is far too low for this.

Any help will be very welcomed.

Solution

  • Assuming you only want to sort columns whose names start with 'Level', you could use something like this:

    Table.Sort(Source, List.Select(Table.ColumnNames(Source), each Text.Start(_, 5) = "Level")