How to combine the values from multiple rows in a column (separated by commas) where there are duplicates in another column

Question

I have a large dataset where I want to combine the values of multiple rows in one column where there are duplicates in another column. See my Example below:

CURRENT DATA

Product Type	Email
Product 1	[email protected]
Product 2	[email protected]
Product 3	[email protected]
Product 4	[email protected]
Product 1	[email protected]
Product 2	[email protected]

REQUIRED DATA

Product Type	Email
Product 1, Product 2, Product 3, Product 4	[email protected]
Product 1, Product 2	[email protected]

The values in the column "Product Type" are combined and separated by commas wherever the Emails are duplicates in the "Email" column.

Answer 1

Power query is available as a free add-in from Microsoft for Excel 2010+.

All you need to do is Group by email and change the m-code to concatenate the relevant rows.

I "Named" the table created "currentData" and you see that referenced in the code.

let
    Source = Excel.CurrentWorkbook(){[Name="currentData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Type", type text}, {"Email", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Email"}, {
        {"Products", each Text.Combine([Product Type], ", "), Text.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"Products", "Email"})
in
    #"Reordered Columns"