Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove all duplicates in query (do not keep any duplicate instance)

Hi guys,   I have a dataset and if there are duplicit values I want to remove them completely.  How can I do that? Standard "remove duplicates" keep one instance of every duplicate, and remove the...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    Anonymous ,

    In Power Query, Duplicate your table, then do a Group By on one table which will count the columns and you will delete any results that are not 1, then merge the tables together on ID, then expand so that you get your original count column, delete all but your original two columns. 

    The pictures are not in exact order, but number 1 is at the bottom


     

     

     

     

     


    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

This M code works without duplicating the table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSixOUdJRMjRVitWJVkpJSwdyzMHssvQSINvUBMxJygBxTAwhnKxSkBYjMCcnvwChvyA/B8SBGFBSlAbimEA5IAMMLcAciJ0QdhGKqpJCIMfIEMkxIMNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Count", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count1", each Table.RowCount(_), type number}}),
    Joined = Table.Join(Source, "ID", #"Grouped Rows", "ID"),
    #"Filtered Rows" = Table.SelectRows(Joined, each [Count1] = 1),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count1"})
in
    #"Removed Columns"

Hope this helps.