Forum Discussion

zraptor's avatar
zraptor
Frequent Visitor
3 years ago
Solved

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

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    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.