Grouping rows by Column and then updating Column

[PeteyG]

I am trying to group all the rows below together and then have all the Built column separated by a comma.  I tried "Group By" but it gets rid of a bunch of my columns and having issues with the comma space.  Anyone have tips.  Vehicle I would use as the main header but everything is the same but where it is built.  Thanks for any help.

I want to turn this:

Vehicle	Color	Int Color	Doors	Year	Seating	Built
Truck	Red	Black	4	26	5	Detroit
Truck	Red	Black	4	26	5	Miami
Truck	Red	Black	4	26	5	Mexico City
Car	Pink	Black	2	25	2	Miami
Car	Pink	Black	2	25	2	Mexico City
Van	White	Black	5	26	8	Detroit
Van	White	Black	5	26	8	Miami
Van	White	Black	5	26	8	London

Into This:

Vehicle	Color	Int Color	Doors	Year	Seating	Built
Truck	Red	Black	4	26	5	Detroit, Miami, Mexico City
Car	Pink	Black	2	25	2	Miami, Mexico City
Van	White	Black	5	26	8	Detroit, Miami, London

[jennratten · Accepted solution]

Hi @PeteyG  - thanks for posting in the Fabric Community. Please review the solution below and let me know if you have any questions.

When you group table data in Power Query, the columns in the original table become lists.  We can see this by choosing the Group By option from the UI menu, selecting Sum, Average, Min or Max as the operation and then looking at the script generated. (The last three create table functions because they are looking at the table rather than just one selected column).

If we choose Max, for example, we see:

Now that we know we are dealing with the column as a list, we can use Text.Combine, which creates delimited text from a list.

= Table.Group(#"Changed Type", {"Vehicle  ", "Color  ", "Int Color  ", "Doors  ", "Year  ", "Seating  "}, {{"Built", each Text.Combine ( [Built], "," )}})

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User

[PeteyG]

You are awesome!!  Thank you so much.  I have been struggling with Group By for two days.  You instructions helped me with where I went wrong.  Thanks again!