Forum Discussion
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 is as follows:
| Database Name | Account Number | Name | Amount |
| CN | 100012 | ABC, Co. Inc. | 50 |
| CN | 100012 | ABC, Co. Inc. (2) | 100 |
| CN | 100012 | ABC | 200 |
My desired result is:
| Database Name | Account Number | Name | Amount |
| CN | 100012 | ABC, Co. Inc. | 350 |
Any help is GREATLY appreciated!!
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.
5 Replies
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.
olgad THANK YOU SO MUCH!! It worked PERFECTLY!!
hi Anonymous ,
try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSUxKLE5V8EvMTVXSUXJMTs4vzStR8CvNTUotAgrAxHNBwkqxOtFKzn5AvqGBgYGhEUjCyVlHwTlfT8EzL1kPyDc1IKRIQcNIEyKJXSWQNALJxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Database Name", type text}, {"Account Number", Int64.Type}, {"Name", type text}, {"Amount", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Database Name", "Account Number"}, {{"Amount", each List.Sum([Amount]), type nullable number}, {"Name", each List.Median([Name]), type nullable text}})
in
#"Grouped Rows"If List.Median([Name]) doesn't work, let me know.
Appreciate a thumbs up if this is helpful.
Please accept this as the solution if your query is resolved.
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?
