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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zraptor
Frequent Visitor

Grouping rows by like values and combining subsequent columns

Hello!  I have a problem that I am sure is probably pretty basic but my brain has quit functioning and I can't get past this.  I have a table with two columns.  An ID column and a Service column.  Shown below:

 

IDService
6589597610
8601944012
8601944010
6589597620
6589597630
8601944012
6589597610
8733527420
3059532320
8477790210
8477790230
8477790250
4532039810
6589597610
8885211320

 

As you can see, the ID column has duplicate values, I would like to combine the duplicates in the ID column and then concatenate the Service column for each row as shown below:

 

IDService
6589597610,20,30,10
8601944012,10
8733527420
3059532320
8477790210,30,50
4532039810
8885211320

 

ANy help would be GREATLY aooreciated!!!

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Service", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All services", each Text.Combine([Service], ", ")}})
in
    #"Grouped Rows"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Service", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All services", each Text.Combine([Service], ", ")}})
in
    #"Grouped Rows"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Perfect!  Thank you so much!

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors