Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LloydW
Frequent Visitor

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 NameAccount NumberNameAmount
CN100012ABC, Co. Inc.50
CN100012ABC, Co. Inc. (2)100
CN100012ABC200

 

 

My desired result is:

Database NameAccount NumberNameAmount
CN100012ABC, Co. Inc.350

 

Any help is GREATLY appreciated!!

 

1 ACCEPTED SOLUTION
olgad
Super User
Super User

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.

olgad_0-1673545383267.png

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

olgad_1-1673545501009.png

and there you have it.

olgad_2-1673545532423.png

Hope that helps. 

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

View solution in original post

5 REPLIES 5
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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?

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
adudani
Super User
Super User

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"

 

adudani_0-1673545579396.png

 

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.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash
olgad
Super User
Super User

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.

olgad_0-1673545383267.png

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

olgad_1-1673545501009.png

and there you have it.

olgad_2-1673545532423.png

Hope that helps. 

 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
LloydW
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors