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 mtomova ,
let me try:
the aggregation part in the Table.Group function (2nd argument) is a list is of lists.
Each if the inner lists contain 3 arguments:
1) The name of the column
2) The aggregation function applied on the specific column
3) The type of the aggregated column
What you pasted is not fully correct, instead of 1, it should be the small letter L ("l")
The "l" stand for each column name that this function iterates through and puts it as the first function of the generated list to represent the colum name. Then the "each List.Sum(Table.Column(_, l)" respresents this part from your original query: "
each List.Sum([#"Net cost 0%"])"
So instead of putting the column name in square brackets (which cannot be done dynamically). I use the Table.Column(_, l) syntax for it: The underscore ("_") stands for the groups partition and the "l" stands for the current column name. This will then represent a column like the syntax with the square brackets.
