Forum Discussion
Power Query Group by aggregation with dynamic column names
Hello mtomova ,
you can use this formula for it:Table.Group(Custom1, {"UPRN"}, List.Transform(List.Difference(Table.ColumnNames(#"Changed Type"), {"UPRN"}), (l)=> {l, each List.Sum(Table.Column(_, l)), type nullable number}))This will add any field from your table that is not called "UPRN" as an aggregated field dynamically.
Hi, this worked just fine, thank you for the help!
I am really keen on understanding the details of the code, I could explain to myself everything up to this point:
(1) => {1, each List.Sum(Table.Column( _,1),
if you have the time, could you explain this bit please
Firstly, please note that it's letter l rather than number 1.
Let me explain ImkeF's solution.
Basically, the each & _ pair is just a syntactic sugar for lambda function. So,
each MyFunc(_)
is equivalent to
(param1) => MyFunc(param1)
Now back to the solution. If you always use each & _ syntax, you'll end up with something like:
Table.Group(
TableOfLastStep,
{"UPRN"},
List.Transform(
MyMetricList,
each {_, each List.Sum(Table.Column(_, _)), type nullable number}
)
)
Note here are two underscores inside Table.Column() but they present different things:
- The first underscore represents each filtered table which is received from Table.Group() function.
- The second underscore represents each column name in MyMetricList which is received from List.Transform() function.
However, Power Query is unable to determine which underscore is for which. Therefore, you have to explicit write the each & _ pairs as lambda functions:
Table.Group(
TableOfLastStep,
{"UPRN"},
List.Transform(
MyMetricList,
(MetricName) => {MetricName, (FilteredTable) => List.Sum(Table.Column(FilteredTable, MetricName)), type nullable number}
)
)
Of course you can keep one of the each & _ pair as ImkeF did.
That's all.