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

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.

Hi Anonymous ,

You can implement your demand following steps below :

Step #1: Create a reference table, then merge the two tables on [ID].

Step #2: Expand the table, choose the [Count] to display to get column [Count.1].

Step #3: Group by the [ID] and [Count.1] to get new count column [Count].

Step #4: Filter row for [Count]=1.

Step #5: Remove Column [Count], then you can choose rename the [Count.1] or not.

You can refer to may test pbix.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi guys,

thanks for help.

Nathaniel´s recommandations works for me. Because I have more columns in my original tables and many values are text.

Ashish_Mathur, just out of curiosity, is it possible to deal also with this type of situation? Beause grouping obviously works on numbers or text (count)?

Thanks

Lukas