Power QueryExercises

Join explosion

An unexpected increase in row count after a merge, caused by duplicate keys in the lookup table.

If a key appears twice in the lookup table, every matching row of the first table appears twice after expanding. Totals then double without any error. Checking that the lookup key is unique, with Table.Distinct or a count per key, before merging prevents it.

In the Power Query editor

Compare the row count shown in the status bar before and after the merge's expand step.

Common mistake

Merging against a lookup table that looks unique in the preview. The preview shows only the first rows, so a duplicate key further down goes unnoticed until totals come out too high. Before the merge, group the lookup table by its key and keep rows whose count is above one, or compare Table.RowCount before and after the expand step. When duplicates are real, decide which row wins and remove the others on purpose rather than with a blind Remove Duplicates. A row count that grows after a refresh, with no change to the query, usually means a new duplicate has arrived in the lookup source.

Learn more