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

[Anonymous]

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 others. I want to remove also this one instance

EG I have data set where are duplicates "asd" a "dfg"

ID	Count
asd	15
dfg	7
vgt	54
bht	41
bju	12
lop	15
pol	17
trf	14
trt	18
asd	8
rrf	14
ttq	21
dfg	17

And I want to get this (where are no duplicate IDs):

ID	Count
vgt	54
bht	41
bju	12
lop	15
pol	17
trf	14
trt	18
rrf	14
ttq	21

Thanks 

Lukas

[Anonymous]

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

[Ashish_Mathur]

Hi,

I do not understand your question.

[Ashish_Mathur]

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.

[v-xicai]

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.

[Nathaniel_C · Accepted solution]

@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