Forum Discussion
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 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
Or with Table.Group
= Table.Group(
myTable,
{"key"},
List.Transform(
myList,
each {
"Sum_"& _,
Expression.Evaluate("each List.Sum(["& _ &"])", #shared),
type number
}
)
)Stéphane
2 Replies
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
PivotStéphane
Or with Table.Group
= Table.Group(
myTable,
{"key"},
List.Transform(
myList,
each {
"Sum_"& _,
Expression.Evaluate("each List.Sum(["& _ &"])", #shared),
type number
}
)
)Stéphane