"Power BI Error: The Key Didn't Match Any Rows in the Table"

tags: powerbi, dax, troubleshooting, tutorial canonical_url: https://pbidocs.com/blog/key-didnt-match-any-rows-error The full error reads: The key didn't match any rows in the table This is the opposite problem from a duplicate value error. That one means LOOKUPVALUE() (or a relationship) found too many matching rows. This one means it found zero — the search value you gave it doesn't exist anywhere in the target column. Product Category = LOOKUPVALUE( DimProduct[Category], DimProduct[ProductKey], FactSales[ProductKey] ) Step One: Find Which Values Are Actually Missing Before guessing at a cause, find the specific values that don't match. A quick way: build a calculated column that checks membership directly. Key Exists = CONTAINS(DimProduct, DimProduct[ProductKey], FactSales[ProductKey]) Filter this to FALSE in a table visual alongside FactSales[ProductKey], and you have the exact list of keys causing the problem — the starting point for figuring out which of the causes below actually applies. Cause 1: A Data Type Mismatch The single most common cause. If FactSales[ProductKey] is text and DimProduct[ProductKey] is a whole number (or vice versa), LOOKUPVALUE() won't match them even when the underlying values "look" the same. FactSales[ProductKey] = "1001" (text) DimProduct[ProductKey] = 1001 (whole number) LOOKUPVALUE searching for "1001" in a column of numbers -> no match Fix: make both columns the same type before the relationship or lookup is built — usually in Power Query, on whichever side came in with the wrong type. "Changed Type" = Table.TransformColumnTypes( Source, {{"ProductKey", Int64.Type}} ) Cause 2: Trailing Whitespace or Case Differences For text keys specifically, invisible whitespace or inconsistent casing breaks an exact match even though the values look identical in a visual. FactSales[SKU] = "AB-1001 " (trailing space) DimProduct[SKU] = "AB-1001" Look identical when displayed. Not equal to LOOKUPVALUE. Fix: clean both sides with Text.Trim (and Text.Upper/Text.Lower if casing is inconsistent) in Power Query before the values are used as keys — see M Language for these functions. "Trimmed Text" = Table.TransformColumns( Source, {{"SKU", Text.Trim}} ) Cause 3: The Value Genuinely Doesn't Exist Yet Sometimes it's not a data quality bug — the fact table legitimately references something the dimension table doesn't have. A product was discontinued and removed from DimProduct, but historical FactSales rows still reference its old key. Or a new product started selling before the dimension table's daily refresh caught up. FactSales (2024-2026, includes discontinued products) DimProduct (current catalog only, discontinued products removed) ProductKey 1001 sold in 2024, but DimProduct no longer has it Fix: this is a referential integrity gap, not a formula bug. Either keep discontinued/historical members in the dimension table (with a flag like IsActive = FALSE instead of deleting the row), or add a placeholder row for exactly this case: DimProduct ProductKey | ProductName 1001 | Trail Runner Tire 1002 | Commuter Helmet -1 | Unknown Product