Removing Duplicates

January's revenue for Pune comes out higher than the till's own report. The cause: the Pune manager uploaded one export twice, and the till re-sent a corrected order. Removing duplicates is a single click, but it only does what you want if you first decide what "the same row" means.

The rule

Duplicates are judged only on the columns you select, and the first row wins.

In Power Query, select one or more columns, then Home → Remove Rows → Remove Duplicates. Power Query compares rows on the selected columns only:

• Select every column and only rows identical in every column count as duplicates.
• Select a key such as OrderID and Power Query keeps one row per OrderID — the first it meets — even when the other columns differ.

The comparison is exact. Pune and pune are different values, and so are Pune and Pune with a trailing space. Tidy the text first, then remove duplicates. Before deleting anything, Home → Keep Rows → Keep Duplicates shows only the repeated rows, so you can see what is about to go.

In M the step is Table.Distinct, with the key columns listed:

Here is the Pune extract:

Row 3 is an exact copy of row 1. Row 5 repeats order 1003 with a different quantity. Row 6 differs from row 4 only in the case of one letter.

The shortcut

Before clicking, finish the sentence "One row per ___." The blank is the set of columns you select. "One row per order" → OrderID. "No exact copies" → every column.

The trap is removing duplicates on a key without looking. When two rows share a key but disagree, the first one survives silently, and it may be the wrong one. Keep Duplicates is the look before the leap.

Drills

Answer fast — trust the rule.

Remember

• Duplicates are judged only on the columns you select, and the first row wins.
• Finish "One row per ___" before choosing columns.
• Traps: case and stray spaces hide duplicates, and a key with conflicting rows keeps the first one silently.