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

[LloydW]

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!!

[watkinnc]

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]

hi @watkinnc ,

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?

[adudani]

hi @LloydW ,

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.

[olgad · Accepted solution]

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. 

[LloydW]

@olgad THANK YOU SO MUCH!!  It worked PERFECTLY!!