How Remove duplicates value according other column values

[renatopnovaes]

Hi, I have the following table with the code column and product group.

Product Group	CODE
A	100
A	101
B	102
B	103
C	101
C	104

 In group A and C, I have the same code. I want to remove duplicity, but I want to keep the code in group A.

I want to hit the target below

Product Group	CODE
A	100
A	101
B	102
B	103
C	104

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGxDcFsJzDbCIltDGY7I6mBsE2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Group" = _t, CODE = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Product Group"}, {{"Value", each _}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Product Group", "Name"}}),
    #"Split Tables" = Record.FromTable(#"Renamed Columns"),
    #"Transformed C" = Table.SelectRows(#"Split Tables"[C], let Code_A = #"Split Tables"[A][CODE] in each not List.Contains(Code_A, [CODE])),
    Custom = Table.Combine(Record.ToList(Record.TransformFields(#"Split Tables", {"C", each #"Transformed C"})))
in
    Custom

[v-jingzhang]

Hi @renatopnovaes 

Has the problem been solved? If so, you may accept the appropriate post as the solution or post your own solution to help other members find it quickly. If not, please provide more details about your problem. Thanks.

Regards,
Community Support Team _ Jing

[AlB · Accepted solution]

Hi @renatopnovaes 

Place the following M code in a blank query to see the steps. Grouped Rows is the important step

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGxDcFsJzDbCIltDGY7I6mBsE2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Group" = _t, CODE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Group", type text}, {"CODE", Int64.Type}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"CODE"}, {{"Product Group", each if List.Count(List.Intersect({[Product Group], {"A", "C"}})) = 2 then List.Select([Product Group], each _ <> "C") else [Product Group] }}),

    #"Expanded Product Group" = Table.ExpandListColumn(#"Grouped Rows", "Product Group"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Product Group",{"Product Group", "CODE"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Product Group", type text}})
in
    #"Changed Type1"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MFCK1YGxDcFsJzDbCIltDGY7I6mBsE2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product Group" = _t, CODE = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Product Group"}, {{"Value", each _}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Product Group", "Name"}}),
    #"Split Tables" = Record.FromTable(#"Renamed Columns"),
    #"Transformed C" = Table.SelectRows(#"Split Tables"[C], let Code_A = #"Split Tables"[A][CODE] in each not List.Contains(Code_A, [CODE])),
    Custom = Table.Combine(Record.ToList(Record.TransformFields(#"Split Tables", {"C", each #"Transformed C"})))
in
    Custom