cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LukasBE Regular Visitor
Regular Visitor

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

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

IDCount
asd15
dfg7
vgt54
bht41
bju12
lop15
pol17
trf14
trt18
asd8
rrf14
ttq21
dfg17

 

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

IDCount
vgt54
bht41
bju12
lop15
pol17
trf14
trt18
rrf14
ttq21

 

Thanks 

Lukas

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

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

@LukasBE ,

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


Groupby1234.PNG

 

Groupby123.PNG

 

Groupby12.PNG

 

Groupby1.PNG

 

Groupby.PNG

 


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

View solution in original post

5 REPLIES 5
Nathaniel_C Super Contributor
Super Contributor

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

@LukasBE ,

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


Groupby1234.PNG

 

Groupby123.PNG

 

Groupby12.PNG

 

Groupby1.PNG

 

Groupby.PNG

 


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

View solution in original post

Community Support Team
Community Support Team

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

Hi @LukasBE ,

 

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].

65.png

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

66.png

 

 

 

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.

Super User
Super User

Re: 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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
LukasBE Regular Visitor
Regular Visitor

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

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

 

Super User
Super User

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

Hi,

I do not understand your question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)