Power Query - Aggregate Columns with Dynamic Column Names

[KNP]

Hi,

After much unsuccessful googling, I'm looking for help dealing with aggregating column names that are dynamic.

I'm sure there's a simple pattern or function to do this but I have Friday brain.

Simple scenario, I have a base table, I merge another table and aggregate certain columns from that table.

Depending on a parameter the user sets, the second merged table ("data") column names can change. I need a way to dynamically aggregate based on a distinct list from the second table.

I've attached a simplified example but the main issue is this line:

= Table.AggregateTableColumn(Source, "data", {{"111", List.Sum, "Sum of 111"}, {"222", List.Sum, "Sum of 222"}})

I need the part in bold to be dynamic.

In the example, if you go into the query editor and look at the 'Output' query you can see column 111 and 222 have summed values. 

Then change the 'pValue' parameter to 'me' and look at the 'Output' query. You can see that columns 111 and 222 are still showing. What I would like showing after the parameter is changed is columns 333 and 444.

I realise I could unpivot other columns then merge and group in this example but the actual data is much larger and more complex than this so I'd rather solve this using a function or reusable pattern in M code.

Aggregate Dynamic Column Names.xlsx

Thanks,

Kim

[v-frfei-msft]

Hi @KNP ,

To make the column name dynamicly, please refer to this case.

https://community.powerbi.com/t5/Desktop/Dynamic-column-name-from-its-value/td-p/189442

[KNP]

I don't think this helps me.
It's not about renaming columns.

[KNP]

Hi @ImkeF, 

Do you have any idea how to best acheive this?

I currently have a very poorly performing solution.

Thanks,

Kim

[ImkeF · Accepted solution]

Hi @KNP 

You can use this formula to make the solution dynamic:

    #"Aggregated data"  = Table.AggregateTableColumn(Source, "data", List.Transform(DistinctValue, each {_, List.Sum, "Sum of " &_}))

[simplyflow]

Hi Imke,

I know this is an old topic, but I would like to group by, based on dynamic columns (i.e. I have columns for monthly data  (1 to 12) and based on the current month, some columns does not exists yet).  In fact I would like to avoid hardcoding the 12 months.  According to what I understood the aggregated formula you gave should work for what I intend to do except that DistinctValue in the List.Transform formula is not recogniced and causes an Expression.Error.  Did I miss something?

Thanks for your insights.

Sebastien

[KNP]

Hi Sebastien,

If I understand you correctly, I think I'd approach this with an 'Unpivot' function first, perform your action and then repivot if you need to.

You're better off opening a new question and referencing this one if you need to.

Not sure if Imke will see this unless you actually @ mention her.

If you open a new question and tag @KNP and @ImkeF I'm sure one of us will be able to assist.

[KNP]

Thank you @ImkeF. 

The correct syntax looks so simple when you know it well. 

I really appreciate you taking the time to help with this. 

I will test/implement as soon as I get back to that project before I mark this as the solution. 

Thanks again,

Kim