Check key quality before you rely on it
Prove a key is unique and non-null, and list the offending values when it isn't.
~9 min read · intermediate · Module 9: Duplicates and keys
By Tahir Nas · Examples run in our simulator · Updated
Before this: Remove duplicates on the right key
Merges, relationships and deduplication all assume that a key identifies one row. A customer list where one ID appears twice turns a lookup into a row multiplier. Check the key before you rely on it.
Is it unique?
let
Customers = #table({"CustID", "Name"}, {{"C-01", "Ana"}, {"C-02", "Ben"}, {"C-02", "Benjamin"}, {"C-03", "Cleo"}}),
Unique = Table.IsDistinct(Customers, {"CustID"})
in
UniqueResult
false
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Which values repeat?
A true/false answer tells you there's a problem; a duplicate report tells you what to fix. Group by the key, count, and keep counts above 1:
let
Customers = #table({"CustID", "Name"}, {{"C-01", "Ana"}, {"C-02", "Ben"}, {"C-02", "Benjamin"}, {"C-03", "Cleo"}, {null, "Walk-in"}}),
Counts = Table.Group(Customers, {"CustID"}, {{"Rows", each Table.RowCount(_), Int64.Type}, {"Names", each Text.Combine([Name], " / "), type text}}),
Problems = Table.SelectRows(Counts, each [Rows] > 1 or [CustID] = null)
in
ProblemsResult
| CustID | Rows | Names |
|---|---|---|
| C-02 | 2 | Ben / Benjamin |
| null | 1 | Walk-in |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Near-duplicates
Keys can be duplicates in meaning but not in value: "c-02" and "C-02 " are different text. Check uniqueness on a cleaned copy of the key to find them:
let
Customers = #table({"CustID"}, {{"C-01"}, {"c-02"}, {"C-02 "}}),
Raw = Table.IsDistinct(Customers, {"CustID"}),
Cleaned = Table.IsDistinct(Table.TransformColumns(Customers, {{"CustID", each Text.Upper(Text.Trim(_)), type text}}), {"CustID"})
in
#table({"Check", "Unique"}, {{"Raw key", Raw}, {"Cleaned key", Cleaned}})Result
| Check | Unique |
|---|---|
| Raw key | true |
| Cleaned key | false |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
If the cleaned key isn't unique, clean the key in both tables before merging; otherwise matches will be missed on one side and multiplied on the other.
Run these checks on both sides of a planned merge. A unique, complete key on the lookup side guarantees the merge won't multiply rows; the same checks on the other side tell you how many rows will find no match.
Complete as well as unique
A null key matches nothing useful in a lookup and groups all unknown rows together. The duplicate report above includes null keys deliberately. Decide whether they should be excluded, mapped to an 'Unknown' member, or fixed at source.
Where to find this in Excel
- Excel for Windows
- Select the key column > Home > Keep Rows > Keep Duplicates
- What you see
- Only rows whose key appears more than once are kept, so an empty result means the key is unique.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Working with duplicate values (Microsoft Learn).
Common mistakes
- Merging on a lookup table without checking its key is unique, then wondering why the row count grew.
- Checking uniqueness on the raw key while matching on a cleaned one.
- Ignoring null keys because they don't show up as duplicates.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: View → Column distribution shows distinct and unique counts per column, based on the profiling sample.
- Power BI warns about many-to-many relationships in the model view when a supposed dimension key repeats; checking in Power Query finds the cause earlier.
- The simulator supports Table.IsDistinct with column lists and comparers such as Comparer.OrdinalIgnoreCase.
Next step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Duplicates and keys quiz