Forum Discussion

Berean_50's avatar
Berean_50
Icon for Helper I rankHelper I
4 years ago
Solved

How to group rows dynamically

Given the following input: the goal is to produce the following output where the table is grouped by the static columns Name and Period. The challenging aspect of this task is that Category A,...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    How about this?

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DynamicColumns = List.RemoveItems(Table.ColumnNames(Source), {"Name", "Period"}),
    #"Grouped Rows" = Table.Group(Source, {"Name", "Period"},
        List.Transform(DynamicColumns, (col) => {col, each List.Max(Table.Column(_, col)), type text}))
in
    #"Grouped Rows"

     

    This takes the column names other than "Name" and "Period" and maxes over them in a Group By step.

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
4 years ago

How about this?

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DynamicColumns = List.RemoveItems(Table.ColumnNames(Source), {"Name", "Period"}),
    #"Grouped Rows" = Table.Group(Source, {"Name", "Period"},
        List.Transform(DynamicColumns, (col) => {col, each List.Max(Table.Column(_, col)), type text}))
in
    #"Grouped Rows"

 

This takes the column names other than "Name" and "Period" and maxes over them in a Group By step.

Berean_50's avatar
Berean_50
Icon for Helper I rankHelper I
4 years ago

AlexisOlson 
This is very clever piece of code. Brilliant! I learned something today. Unfortunately all I can afford is to say "Thank You".