Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Consolidate Rows Aggregating some columns and keeping the first record for another column

I have multiple rows of data that I need to consolidate into one row.  Some columns are aggregated (Sum) and ONE column has different values but I need to maintain ONLY the first value.    My data ...
Need Help
  • olgad's avatar
    olgad
    3 years ago

    Hi there,

    you can try getting what you want in Power Query, 
    Transform-Group By-Advanced- for the name pick Max/Min as aggregation, for the amount-sum.

    Once done, go to M-Code and change Max/Min to the First. 

    and there you have it.

    Hope that helps. 

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

You can group by Database Name and Account Number, and choose the Sum aggregation for Amount, and an All Rows aggregation--name that column "Details". Once that's done, add a step:

 

= Table.AddColumn(PriorStepOrTableName, "Name", each [Details]{0})

 

--Nate

adudani's avatar
adudani
Memorable Member
3 years ago

hi Anonymous ,

 

Thanks for the response.

Just curious:

 

this will dynamically adjust based on grouped database name and account number right? or based on the index, it provides only the first value in the details column?