Forum Discussion

jct999's avatar
jct999
Advocate II
2 years ago
Solved

Dynamic grouping with M (PQ)

Hello, I have a table myTable with 21 columns : The first one is the column "key" The others are named "value_1", "value_2" .... "value_20" I have a list that contains a subset of the colum...
Need Help
Tips and Tricks
  • slorin's avatar
    slorin
    2 years ago

    Or with Table.Group

    = Table.Group(
      myTable,
      {"key"},
      List.Transform(
         myList,
         each {
            "Sum_"& _,
            Expression.Evaluate("each List.Sum(["& _ &"])", #shared),
            type number
            }
         )
      )

    Stéphane

slorin's avatar
slorin
Super User
2 years ago

Hi, jct999 

 

Select Columns, Unpivot then Pivot

 

let
Source = myTable,
SelectColumns = Table.SelectColumns(Source,{"key"} & myList),
UnPivot = Table.UnpivotOtherColumns(SelectColumns, {"key"}, "Attribute", "Value"),
Pivot = Table.Pivot(UnPivot, List.Distinct(UnPivot[Attribute]), "Attribute", "Value", List.Sum)
in
Pivot

Stéphane