Group by one column and merge contents of duplicate columns

[Krc721]

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!

[Krc721 · Accepted solution]

@amitchandak 

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.

[amitchandak]

@Krc721 . Try a new column like

Concatenatex(Distinct({[Product Builder Name],[Project Quality Control Name],[Product Owner],[Product Tester], [Product Shipper]}), [Value], ", ")

[Krc721]

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