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. 

     

adudani's avatar
adudani
Memorable Member
3 years ago

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.