Expression.Error: The key matched more than one row in the table
By Tahir Nas · Examples run in our simulator · Updated
Expression.Error: The key matched more than one row in the table.
A row lookup like Table{[Key = "A"]} requires exactly one matching row. The table has duplicates of that key, so M refuses to guess which row you meant.
Common causes
- Duplicate keys in a lookup or mapping table.
- A workbook navigation step where two objects share a name (a sheet and a table both called Sales).
- Keys that differ only by trailing spaces become duplicates after Trim.
How to fix it
- Remove duplicates on the key column before looking up: Table.Distinct(table, {"Key"}).
- If several matches are valid, filter with Table.SelectRows and take the first deliberately with Table.First or {0}.
- In navigation steps, also match on Kind: Source{[Item = "Sales", Kind = "Table"]}[Data].
Find the step that fails
- Open the query in the Power Query editor (Excel: Data → Queries & Connections → Edit; Power BI Desktop: Transform data).
- Click the steps in Applied Steps from the top. The first step that shows the error is where it starts; later steps only repeat it.
- Read that step's formula in the formula bar (View → Formula Bar if it's hidden) and compare it with the columns and values the previous step produced.
Minimal example
Raises the error:
#table({"Code", "Rate"}, {{"EUR", 0.85}, {"EUR", 0.86}}){[Code = "EUR"]}[Rate]Fixed:
Table.Distinct(#table({"Code", "Rate"}, {{"EUR", 0.85}, {"EUR", 0.86}}), {"Code"}){[Code = "EUR"]}[Rate]Both snippets are run by the Power Query Exercises simulator on every build: the first must raise Expression.Error, the second must run.
Practise avoiding it
- beginner~6 minPro
Keep one signup per email address (newsletter)
Build a mailing list with one row per email address from signups that contain repeats.
- advanced~10 minPro
Keep each person's most recent signup (newsletter)
For every email keep only the row with the latest Signed date, whatever the row order.
- beginner~6 minPro
Find invoice numbers that appear more than once (accounts payable)
Before loading, list every invoice number that occurs more than once and how many times.
- intermediate~7 minPro
Count each email once, whatever its case or spacing (petition)
The same address appears as Amara@Mail.example and ' amara@mail.example'. Build a clean list of unique emails.
Related lessons
- Check key quality before you rely on it
- Remove duplicates on the right key
- Import from Excel: tables, sheets and the navigation table