Merge Two Records (Rows)

[Einomi]

Hello Everyone,

I have a large dataset where I notice that I have few product codes that are duplicated because they do not have the same department.

For example, I have 1999 twice because they do no have the same department

Code	Name	Dept
1999	Apple Kugel	kugels
1999	Apple Kugel	kugel

for some reasons sometimes I have the quantity on the first 1999 sometimes on the second one

I would like to merge these two records, not to sum them

Out of 80 product codes, I have 4 duplicates (for the same reason, the dept is not the same)

Thanks for your help, I hope I was clear enough

[Einomi · Accepted solution]

@BIHelper thanks for your time, this solution seems a bit complicated for me. Instead, I have made efforts to correct the source and remove all duplicates by replacing the values. Thanks for your time

[BIHelper]

@Einomi -You need to do group by on the basis of  DNDCode in which you need to do sum aggregation for ProductName and Department and all rows for remaining Columns.
then you need to create custome columns for all columns except ProductName and Department which will convert it into list and then you have to extract the values from that column
OR
Try this code,
#"Grouped Rows" = Table.Group(#"Changed Type1", {"DND Code"}, {{"ProductName", each Text.Combine([ProductName],", "), type nullable text}, {"Department", each Text.Combine([Department],", "), type nullable text}, {"45", each _, type table [DND Code=nullable number, ProductName=nullable text, Department=nullable text, 45=nullable number, 44=nullable number, 43=nullable number, 42=nullable number, 41=nullable number, 40=nullable number]}, {"44", each _, type table [DND Code=nullable number, ProductName=nullable text, Department=nullable text, 45=nullable number, 44=nullable number, 43=nullable number, 42=nullable number, 41=nullable number, 40=nullable number]}, {"43", each _, type table [DND Code=nullable number, ProductName=nullable text, Department=nullable text, 45=nullable number, 44=nullable number, 43=nullable number, 42=nullable number, 41=nullable number, 40=nullable number]}, {"42", each _, type table [DND Code=nullable number, ProductName=nullable text, Department=nullable text, 45=nullable number, 44=nullable number, 43=nullable number, 42=nullable number, 41=nullable number, 40=nullable number]}, {"41", each _, type table [DND Code=nullable number, ProductName=nullable text, Department=nullable text, 45=nullable number, 44=nullable number, 43=nullable number, 42=nullable number, 41=nullable number, 40=nullable number]}, {"40", each Table.RowCount(_), Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([45],"45")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Column([44],"44")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.Column([43],"43")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Table.Column([42],"42")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each Table.Column([41],"41")),
#"Extracted Values" = Table.TransformColumns(#"Added Custom4", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Extracted Values2" = Table.TransformColumns(#"Extracted Values1", {"Custom.2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Extracted Values3" = Table.TransformColumns(#"Extracted Values2", {"Custom.3", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Extracted Values4" = Table.TransformColumns(#"Extracted Values3", {"Custom.4", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(#"Extracted Values4",{"45", "44", "43", "42", "41"})
in
#"Removed Columns"
Then You will be able to see this result

Mark this as a soluntion if it solved your problem.

Thank You!