Forum Discussion

mraka9's avatar
mraka9
Icon for Helper III rankHelper III
4 years ago
Solved

Remove Duplicates

Greeting, I have a table with two columns. Would like to remove duplicates. Product1 is related to Product2. The problem is that there are duplicates, but they have been changed in the columns. ...
Need Help
  • ddpl's avatar
    ddpl
    4 years ago

    mraka9 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NDQ0VNIBMYyMjJRidaKVnJycDAwMgGLOzs4gSZAYVFoHpgEkBpXWgWmIjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product1 = _t, Product2 = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns", {"Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
    #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"Product1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Product2] <> null))
    in
    #"Filtered Rows"

     

     

    Please check and accpet as solution if its worked.

     

ddpl's avatar
ddpl
Icon for Solution Sage rankSolution Sage
4 years ago

mraka9 

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NDQ0VNIBMYyMjJRidaKVnJycDAwMgGLOzs4gSZAYVFoHpgEkBpXWgWmIjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product1 = _t, Product2 = _t]),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns", {"Value"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 1, 1, Int64.Type),
#"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
#"Filled Down" = Table.FillDown(#"Removed Columns",{"Product1"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Product2] <> null))
in
#"Filtered Rows"

 

 

Please check and accpet as solution if its worked.

 

mraka9's avatar
mraka9
Icon for Helper III rankHelper III
4 years ago

Thank you very much!
This could be the solution when I apply to a large amount of data!