Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KNP
Super User
Super User

Power Query - Aggregate Columns with Dynamic Column Names

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
1 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 " &_}))

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

7 REPLIES 7
v-frfei-msft
Community Support
Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

I don't think this helps me.
It's not about renaming columns.
Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Hi @ImkeF

 

Do you have any idea how to best acheive this?

I currently have a very poorly performing solution.

 

Thanks,

Kim

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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 " &_}))

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors