Lesson 8 of 15 · Lab
Finding and removing duplicate rows
To find duplicate rows, count how often each key appears, and anything above one is a duplicate. To remove them, keep one row per key. The harder part is choosing which copy is true, because copies often disagree.
Exact duplicates match in every column, while key duplicates share only an identifier. Rows 101, pending and 101, booked are key duplicates, and they have to be resolved by a rule.
Here are fictional Vandelay Coffee Supply's 18 clean invoice rows, sorted newest export first.
| invoice_id | exported_on | status | amount_eur |
|---|---|---|---|
| INV-1008 | 2026-09-01 | pending | 540 |
| INV-1008 | 2026-10-01 | booked | 540 |
| INV-1010 | 2026-09-01 | booked | 900 |
| INV-1010 | 2026-10-01 | booked | 900 |
Unique keeps the first row per invoice_id. If you reverse the sort, does the total change?
No, and that's the trap. Both copies of each invoice carry the same amount, so the total can't see which one you kept.
Remove duplicates, then reverse the sort
- Here, in a live flow in Strata, Oplema's browser app for data prep, Unique is selected. Output Unique shows one row per invoice, with INV-1008
bookedat the top. - Open Output Duplicate. These two rows are what you removed. Keeping them visible means you can show your work.
- Select Summarize, the last block. Note
totalandrows. - Select Sort. In the first key, change Descending to Ascending.
- Select Summarize: same numbers. Now select Unique, open Output Unique and scroll to INV-1008. Its status has flipped.
- Put the sort back to Descending and check that INV-1008 reads
booked.
Swipe sideways to see the canvas, or open this page on a larger screen to work with it.
What each output holds
Unique keeps 16 rows totaling €14,410, and Duplicate holds 2 rows totaling €1,440, in either direction. Reversed, INV-1008 reads pending.
First means first in the order you gave it
The position of a row in a file proves nothing. Here you have a real reason for the order, since a later export knows more. The status matters because filtering rows keeps only booked invoices, so the wrong copy would quietly cost €540.
If copies disagree on something you can't rank, sorting won't settle it. Unique's Split mode sends every copy to Duplicate so you can ask the source's owner.
The total falls from €15,850 to €14,410 here. That €1,440 is the overlap between the two exports, and Vandelay's sales report counted it twice. The finance report's €10,168 is still unexplained.
Common duplicate mistakes
- Choosing too small a key. In a table of invoice lines,
invoice_idalone deletes real sales, so you'd wantinvoice_idplusline_id. Ask what one row should represent. - Removing without looking. Keep the removed rows where you can read them.
- Leaving the survivor to chance. Without a stated sort, the copy you keep depends on the file.
Duplicates in SQL and Excel
SELECT invoice_id, COUNT(*) FROM invoices
GROUP BY invoice_id HAVING COUNT(*) > 1;
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY invoice_id ORDER BY exported_on DESC) AS rn
FROM invoices
) t WHERE rn = 1;The first query finds duplicate keys, and the second keeps the newest copy of each. SELECT DISTINCT removes only exact duplicates. In Excel, =COUNTIF(A:A, A2)>1 flags repeats. Remove Duplicates keeps the first occurrence, so sort first, and it reports only how many rows it removed.