Forum Discussion
How to hide/remove duplicates based on condition
Hi, how do I hide/remove duplicate rows based on the following condition:
- if [JobID+Ticket] column contains duplicate, keep row with the highest [Sequence] number and remove the other duplicate rows. Screenshot example below - keep Sequence "4676003" and delete the lower sequences above it
I'm guessing that it eliminated duplicates for each file individually but there were duplicates across files. I guess you do have to de-duplicate after combining the files then.
In this case, you use the same code (with any step references adjusted, e.g. #"Merge Columns" --> #"Inserted Merged Column") but put it at the end of the FreightForward v2 query rather than the Transform Files (2) function.
If you're going to use a database eventually, you might want to try approach #4 from my blog post.
14 Replies
As per my understanding the funcion "remove duplicates" works from top to bottom, so all you need to do is:
- to append the new data to the old one (you will have now duplicates in your key column
- sort them by the column you desire (for example if you need the most recent, sort it descending by update date, or If you need the highest sequence number, sort them in descending number). The important is that the record you want to keep is on a higher row than the one you want to remove
- now right click on the key column and remove duplicates
Sometimes the easiest solution is the best 🙂
I recently wrote a blog post on this:
Select Distinct Rows Ordered by Another Column -- Power Query EditionAlexisOlson, thanks for sharing your post. Its a beast! Which method would you recommend for my case? My datasource is importing spreadsheets from sharepoint with each file containing an average of ~300k rows, current table rows at 3M+ rows.
I tried option 4 from your blog post but I'm getting an error "after merging with the step before the grouping". BA_Pete , mahoneypat any suggestions?
Appreciate any help please.
Link to file if you need
https://drive.google.com/drive/folders/1dmmbhdbzySZBr8oTxj9CWsmztwFQ3f8A?usp=sharing
