Forum Discussion

mtomova's avatar
mtomova
Helper III
3 years ago
Solved

Power Query Group by aggregation with dynamic column names

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 adde...
Need Help
  • ImkeF's avatar
    ImkeF
    3 years ago

    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.

ImkeF's avatar
ImkeF
Community Champion
3 years ago

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's avatar
Di77
Frequent Visitor
3 years ago

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