Forum Discussion

KNP's avatar
KNP
Icon for Super User rankSuper User
7 years ago
Solved

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

    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 " &_}))
KNP's avatar
KNP
Icon for Super User rankSuper User
7 years ago
I don't think this helps me.
It's not about renaming columns.
KNP's avatar
KNP
Icon for Super User rankSuper User
7 years ago

Hi ImkeF, 

 

Do you have any idea how to best acheive this?

I currently have a very poorly performing solution.

 

Thanks,

Kim

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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 " &_}))
    • KNP's avatar
      KNP
      Icon for Super User rankSuper User
      7 years ago

      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

    • simplyflow's avatar
      simplyflow
      New Member
      4 years ago

      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's avatar
        KNP
        Icon for Super User rankSuper User
        4 years ago

        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.