How Remove duplicates value according other column values

 

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

 

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.