Single record with concatenated data (field)

[jerryr125]

Hi - 

I have the following data in which I would like to run through Power Query to create a single record for each ID.

Example:

Data Start (Table A)

ID	Item
1	Pizza
1	Hamburger
2	Banana
3	Ice Cream
3	Pizza
3	Apple
3	Bread
4	Rice
4	Pasta

New Table (Table B) - after running through Power Query:

,

ID	Item
1	Pizza; Hamburger
2	Banana
3	Ice Cream; Pizza; Apple; Bread
4	Rice; Pasta

Any thoughts on how this can be accomplished ?

Thanks - Jerry

[Anonymous]

Hi @jerryr125 ,

Both answers of @p45cal and @Omid_Motamedise are great! Please remember to accept the reply as solution if you have solved your problem to help more others facing the same problem to find a solution quickly, thank you very much!

Best Regards,
Dino Tao

[Omid_Motamedise · Accepted solution]

right click on the Id column and pick group by command and make the setting presented in the next image

press ok will result in error in the new column as it is not posible to apply sum operation on the text as the next

in the formula replaced list.sum by Text.combine to reach the next

if you want to use seperator also write the formula as bellos.

Text.Combine([Item],"; ")

[jerryr125]

excellent information - thank you !

[p45cal]

let
    Source = Excel.CurrentWorkbook(){[Name="JerrysTable"]}[Content],
    GroupedRows = Table.Group(Source, {"ID"}, {{"Item", each Text.Combine(_[Item],"; ")}})
in
    GroupedRows