Power Query Group by aggregation with dynamic column names

[mtomova]

Hi all,

I am trying to perform a group by in Power Query, which works fine if I use the interface.

However, I am aggregating by certain columns, but in the future there will be more columns added to the source file, and none of them will be included in the current step, where Power Query has hardcoded the selected column names.

Is there a way to make the aggregation dynamic, so that each new column that is included in the source file will be added in Power Query end result.

This is the code that Power Query have generated when I was  using the interface:

= Table.Group(Custom1, {"UPRN"}, {
{"Net cost 0%", each List.Sum([#"Net cost 0%"]), type nullable number}, 
{"Net cost 5%", each List.Sum([#"Net cost 5%"]), type nullable number}, 
{"Net cost 20%", each List.Sum([#"Net cost 20%"]), type nullable number}, 
{"PV", each List.Sum([PV]), type nullable number},
{"DMEV", each List.Sum([DMEV]), type nullable number},
{"ASHP8", each List.Sum([ASHP8]), type nullable number}, 
{"CWI", each List.Sum([CWI]), type nullable number}, 
{"LITU", each List.Sum([LITU]), type nullable number}, 
{"DPLH", each List.Sum([DPLH]), type nullable number}, 
{"HCCTRV", each List.Sum([HCCTRV]), type nullable number}, 
{"HSS7+HP", each List.Sum([#"HSS7+HP"]), type nullable number}})

I have tried to do it on my own, but at the moment my understanding of M language is very weak.

Any help will be highly appreciated

Many thanks,

Maria

[ImkeF]

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.

[mtomova]

Hi @ImkeF , thank you for the detailed explanation, it really helped me understand.
I am sure many users of the forum will find it useful!

[ImkeF · Accepted solution]

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.

[Di77]

Thank you for your explanation, it's really helpful. I have 1 question in the Table.ColumnNames function you refer to #"Changed Type" step and I'm not sure what was done at that step. Do you mind explaining that part? I understand Custom1 is the previous step.

Also in my case I'd like to return all rows instead of sum. Should i just remove List.Sum function?

Thanks in advance

thanks in advance

[mtomova]

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

[WSF]

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.