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

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

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.