Forum Discussion

theo's avatar
theo
Helper III
7 years ago

Count duplicate values using switch or if statement

Hi.  I have been bugging witht this problem to count duplicates in multiple columns involving 12millions rows and counting.  I am trying now with the approach which does not provide me the correct re...
Need Help
Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi theo 

Can you send some data sample?

 

Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski

 

  • theo's avatar
    theo
    Helper III
    7 years ago

    Hi Mariusz ;

    Here's a representative sample  and some manual calculations I did.

    • Mariusz's avatar
      Mariusz
      Community Champion
      7 years ago

      Hi theo 

       

      You can achieve this by applying three steps in Query editor.

      Please see the M code below based on the example that you provided.

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY+xDcQwDAN3cZ0iEpnEmSXw/mu8zm4ej3xBwQB5lPw8LdrWsqSSS0fpbGN7d67p9HrdpdgZ5IJg6J8LHsdqpSZOxsUgHcRzf/NzXkF7rvbknKQwSSbJnMn+69MqjhAlyukLX/iCF7zg1b98UIMa1Oz32m9QgxrUoOYTvtsYHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1))
in
    #"Filtered Rows"
      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      Mariusz Repczynski

       

      • theo's avatar
        theo
        Helper III
        7 years ago

        hi Mariusz Thanks but it doesnt provide the result that I need.

        The code you provided just count the duplicate of all entry .

        What I need is to count the rows that have duplicate (eg. 5 duplicates) based on the current row.

        in my example under dup 5 count, line 1 has a count of 1 since there is one row that has 5 similar entry with line 1 by virtue of 1,2,3,4, and 5.  same goes with line 2 while the rest has 0 meaning no row in the table that has 5 similar entry.