Dynamic grouping with M (PQ)

[jct999]

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 column names of myTable : myList = {"value_6", "value_12", "value_18"}
This list is not frozen. It can change, with 1, 2, 3, 4, 5... items.

How can I apply a group by calculation on myTable for the subset of columns names present in myList ?

It should be something like :

myStep = Table.Group(
myTable,
{"key"},
... here create on the fly a list of aggregation descriptions based on myList ... (aggregation function be a sum) ...
)

And I'm interesting in having a deep understanding on how M(PQ) iterate over the aggregation description list in the Table.Group function.

thanks for 

[slorin · Accepted solution]

Or with Table.Group

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

Stéphane

[slorin]

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