Forum Discussion

dudeyates's avatar
dudeyates
Frequent Visitor
7 years ago

Using Power Query and M to Delete Duplicates Based on Certain Criteria

I'm trying to figure out how to remove duplicate rows based on certain criteria. If one of the rows matches the criteria, I keep it and delete the duplicates. I can do this in VBA, but I really want ...
Need Help
v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

Hi dudeyates ,

You could refer to Remove Duplicates function in query editor:

Result:

You could also download the pbix file to have a view.

 

Regards,

Daniel He

dudeyates's avatar
dudeyates
Frequent Visitor
7 years ago

v-danhe-msft Thanks for taking the time took look at this. I think that works because coincidentally the sort puts af2 16700 above the duplicate to be removed. But what if the first duplicate had af2 of 12345, and the af2 of 16700 I want to keep ends up being the 2nd duplicate, does the af2 16700 get removed? 

 

For the vast majority of my data, the straight forward Remove Duplicates works. But there are instances when the line I want to keep is the 2nd of the duplicates, and I actually want to remove the first duplicate.  And maybe I'm just missing something in your example (I have downloaded and viewed)

 

Thanks,

James