- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Group by one column and merge contents of duplicate columns
Hi,
I need to process an excel file in power bi desktop/power query where there may be one or more of the same value in column a. For each row there are people assigned to the object in column a. I need to be able to concatenate all the people into one cell separated by semi-colon and have only one line for each item in column a.
Ex.
Product Name Product Builder Name Project Quality Control Name Product Owner Product Tester Product Shipper
Game 1 Joe Smith John Smith Jane Smith Jill Smith Bob Jones
Game 1 Joe Jones John Smith Jane Smith Jill Smith John Jones
Game 2 Scooby Doo Shaggy Velma Daffney Freddie
Output
Game 1 Joe Smith; Joe Jone John Smith Jane Smith Jill Smith Bob Jones
Game 2 Scooby Doo Shaggy Velma Daffney Freddie
So one record for Product, concatenating multiple names when different, one name when the same across multiple rows.
Thanks!
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I believe I solved it. I made duplicate copies of the query in Power BI. Then I selected only the role and product name for each role. Then I deduped each one selecting the Product Name and Role Name (except in master query) so that multiple instances of the same product with same person were gone. Then I removed the nulls and did some other cleanup and then did the group by on the product changing the Sum to the Text.Combine feature and adding the proper seperator and the word nullable in the last part of the expression for each of the role tables. When they were imported back into Power BI, checked the data model and all were connected back to the master table. The Master table was deduped based only on product name after removing the role columns.
The master table had all data except for the roles.
Each role had only one other column, product name
Then built the report in table visual in Power BI.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I believe I solved it. I made duplicate copies of the query in Power BI. Then I selected only the role and product name for each role. Then I deduped each one selecting the Product Name and Role Name (except in master query) so that multiple instances of the same product with same person were gone. Then I removed the nulls and did some other cleanup and then did the group by on the product changing the Sum to the Text.Combine feature and adding the proper seperator and the word nullable in the last part of the expression for each of the role tables. When they were imported back into Power BI, checked the data model and all were connected back to the master table. The Master table was deduped based only on product name after removing the role columns.
The master table had all data except for the roles.
Each role had only one other column, product name
Then built the report in table visual in Power BI.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

@Krc721 . Try a new column like
Concatenatex(Distinct({[Product Builder Name],[Project Quality Control Name],[Product Owner],[Product Tester], [Product Shipper]}), [Value], ", ")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi Amit. This is great for another use case I had. Thank you. However, my apologizes for not being clear. It is the same column across multiple rows. So in the example. Game 1 is the product for the group by. They want one row by Game 1 and then there are two different product builders and they want those on one cell called product builder separated by semi colon. Where there is only one person they want the name to show only once. If the column is empty it should show empty. The data will be presented in Power BI in table form. I tried using group by and then switch the List.Sum to Text.Combine and add seperator; however, it duplicates where same name is in multiple roles and puts the separator where there are nulls. In some rows, the product may be listed with as many as 5 in the same role.
I was able to do it in Alteryx and tried to use a similar methodolgoy in Power BI as that is where it has to be built and I am not having success as there is a separator in empty columns and any Product that has several lines with the same person in the role on multiple lines concatenates duplication of name.
Product Product Builder Product Quality Control Product Owner Product Tester Product Shipper
Game 1 Joe Smith,Joe Jones John Smith Jane Smith Jill Smith Bob Jones, John Jones

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
08-28-2024 01:31 AM | |||
10-02-2024 10:08 AM | |||
08-22-2024 09:13 AM | |||
09-04-2024 10:26 PM | |||
08-20-2024 05:37 PM |
User | Count |
---|---|
83 | |
77 | |
40 | |
40 | |
35 |