Forum Discussion

renatopnovaes's avatar
5 years ago
Solved

How Remove duplicates value according other column values

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 co...
  • CNENFRNL's avatar
    5 years ago

     

    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

     

  • AlB's avatar
    5 years ago

    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.