Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

keep rows containing duplicates in column 1 OR column 2

Hi all,

 

I would need to keep the rows that contain a duplicate in column 1 OR column 2. 

In the easy example below, I should keep rows 1,2,4 and 5 as 1 and 2 contain a duplicate in column 1 and row 4 and 5 have a duplicate in column2. 

 

Thank you in advance.

 

Column1            Column2
123                    23
123                    45
231                    66
432                    77
789                    77

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzJDQAhDAN7yZsHkAtqQem/DbCllfbhKBof58iYKk14xpO6VPuoOaynMNKpiETAwrNITRHJRJsrgLn2H+5OGsoe8pj2kKoL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    Cols = Table.ToColumns(Source),
    Total = List.Count(Cols),
    #"Added Custom" = Table.AddColumn(
        Source, 
        "Duplicate", 
        (r) => List.LastN(
                List.Generate(
                () => [cnt=0, sn=0],
                each [sn]<Total and [cnt]<=1,
                each [cnt=List.Count(List.PositionOf(Cols{[sn]}, Record.ToList(r){[sn]}, Occurrence.All)), sn=[sn]+1],
                each List.Count(List.PositionOf(Cols{[sn]}, Record.ToList(r){[sn]}, Occurrence.All))
            ),1
        ){0}>1
    )
in
    #"Added Custom"

     

2 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYzJDQAhDAN7yZsHkAtqQem/DbCllfbhKBof58iYKk14xpO6VPuoOaynMNKpiETAwrNITRHJRJsrgLn2H+5OGsoe8pj2kKoL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    Cols = Table.ToColumns(Source),
    Total = List.Count(Cols),
    #"Added Custom" = Table.AddColumn(
        Source, 
        "Duplicate", 
        (r) => List.LastN(
                List.Generate(
                () => [cnt=0, sn=0],
                each [sn]<Total and [cnt]<=1,
                each [cnt=List.Count(List.PositionOf(Cols{[sn]}, Record.ToList(r){[sn]}, Occurrence.All)), sn=[sn]+1],
                each List.Count(List.PositionOf(Cols{[sn]}, Record.ToList(r){[sn]}, Occurrence.All))
            ),1
        ){0}>1
    )
in
    #"Added Custom"

     

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyPrRAAYyUdJQgTCNjpVgd7FImpmApI2NDDCkzM7CUibERhpS5OVjK3MISq1QsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column1 " = _t, Column2 = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1 ", "Column1"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let thiscol1 = [Column1] in List.Count(List.Select(#"Changed Type"[Column1], each _ = thiscol1))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each let thiscol2 = [Column2] in List.Count(List.Select(#"Changed Type"[Column2], each _ = thiscol2))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Keep", each if [Custom]>1 or [Custom.1]>1 then "Y" else "N"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Keep] = "Y")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Custom.1", "Keep"})
    in
    #"Removed Columns"

     

    Pat