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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.