Forum Discussion
Grouping rows by Column and then updating Column
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 |
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
2 Replies
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
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!
