Power Query: Group by - Output Multiple Sum Columns

[takoyaki]

Hello all,

I'm currently working on a query that will eventually feed into a chart. The original table was too large and exceeded the chart's maximum 255 data series so I wanted to rewrite the query to group the rows by multiple columns with constant names. The issue is that the header names of the columns with values to be summed are variable, as is the amount of columns to sum.

Illustration of my desired result:

Here is the code so far; it is currently able to create a list of the DateHeaders and divide the values in them by the Hours in the Lookup table but doesn't currently have the capability of grouping rows.

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
    DateHeaders = List.Buffer(List.Select(Table.ColumnNames(Table1), each try Value.Is(Date.From(_), type date) otherwise false)),
    Hours = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Hours"]}[Content],{{"Period", type date}}),
    Division = List.Accumulate(DateHeaders, Table1, (s,c) =>
        Table.TransformColumns(s, {{c, each _/Hours{[Period = Date.From(c)]}[Hours]}}))

in
    Division

I can easily use the UI to Group By but I am unsure how to rewrite the generated M-Code to create sum columns for all of my DateHeaders columns.

Group = Table.Group(Table1, {"Column1", "Column2", "Column2"}, {{"1/1/2022", each List.Sum([#"1/1/2022"]), type nullable number}})

Any help would be very much appreciated!

 

[takoyaki]

I did a bit more research and found the solution:

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
    DateHeaders = List.Buffer(List.Select(Table.ColumnNames(Table1), each try Value.Is(Date.From(_), type date) otherwise false)),
    Hours = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Hours"]}[Content],{{"Period", type date}}),
    Group = Table.Group(Table1, {"Column1", "Column2", "Column3"}, List.Transform(DateHeaders, (C) => {C, each List.Sum(Table.Column(_,C))})),
    Division = List.Accumulate(DateHeaders, Group, (s,c) =>
        Table.TransformColumns(s, {{c, each _/Hours{[Period = Date.From(c)]}[Hours]}}))

in
    Division