How to make duplicated value to aggregate as one and merge the other column value?

[marry]

Hello,

I have a table in specfic column (Site Code Merge Custom) contains the duplicated values, can someone advise how to create a new table that show like below? In column "Site Code Merge Custom" I only want the duplicated value show in one record, and the "Manufacturing Product Location Code" column value merge and use coma to separte out.

Thanks in advance.!

[marcelsmaglhaes · Accepted solution]

Hey @marry ,

You can combine the Row using Text.Combine function on Power Query.

First, you need to "group by whit sum" your table like the image bellow.

Then, that will generate an error

To fix the error, you need edit the M code like that:

Table.Group(#"Changed Type", {"Site Code"}, {{"Location Code.1", each Text.Combine([Location Code], ","), type nullable text}})

You will achieve your lines combine!

[marry]

Hello @marcelsmaglhaes ,

Amazing!!!! Thank you so much!! It fully filfill my need. 🙂

Appreciated your help!

Best regards,

Mary Chen