DEV Community: PBIDocs

Power Query Error: We Couldn't Convert to Number (or Date)

PBIDocs — Tue, 25 Aug 2026 20:07:29 +0000

The instinct is to assume the source data is just messy. Sometimes it is — but just as often, the value is perfectly valid and Power Query is parsing it under the wrong assumptions.

Step One: Isolate the Actual Bad Rows

Don't guess. After the failing type-conversion step, right-click the column header and choose Keep Errors — this filters the table down to only the rows that failed, instead of scrolling to find them.

Full table (10,000 rows)
        |
        | Keep Errors, after the type conversion step
        |
Just the rows that failed (often a handful, sometimes just one pattern)

Cause 1: A Locale Mismatch on the Decimal Separator

The most common cause with numbers specifically. 1.234,56 is a perfectly valid number in most of continental Europe — comma as the decimal separator, period as the thousands separator. Power Query, using a different default locale, reads that same text as 1.234 followed by garbage, and fails.

Value in source: "1.234,56"   (intended: one thousand, two hundred thirty-four point five six)
Parsed with US locale (period = decimal): fails or misreads entirely
Parsed with the correct locale: 1234.56

Fix: convert with an explicit locale rather than the default. Right-click the column, Transform > Using Locale, and pick the locale the source data actually uses.

#"Changed Type with Locale" = Table.TransformColumnTypes(
    Source, {{"Amount", type number}}, "de-DE"
)

Cause 2: An Ambiguous Date Format — and the Silent Version Is Worse

13/04/2026 fails loudly, because no locale reads month 13 as valid — that's the easy case. The genuinely dangerous version is a date like 03/04/2026, which is valid in both DD/MM/YYYY and MM/DD/YYYY — one means March 4th, the other April 3rd. If the locale assumption is wrong, this parses successfully into the wrong date, with no error at all.

"03/04/2026" with DD/MM locale -> April 3rd
"03/04/2026" with MM/DD locale -> March 4th

Both "succeed." Only one is correct. Nothing flags the other.

Fix: the same locale-aware conversion as Cause 1 — but critically, this cause won't show up in a Keep Errors check, since nothing errors. If dates look off by a matter of days or months in a way that smells like this, check the locale explicitly rather than trusting the absence of an error.

#"Changed Type with Locale" = Table.TransformColumnTypes(
    Source, {{"OrderDate", type date}}, "en-GB"
)

Cause 3: A Stray Non-Numeric Value

The straightforward version: a column that's otherwise numbers has a handful of rows containing "N/A", "TBD", "-", or an empty string — often placeholders someone typed in a spreadsheet for "not applicable yet."

Amount
1200
850
N/A       <- this row fails the whole conversion
430

Fix: replace the placeholder with null (or a real default) before converting type, not after.

#"Replaced Value" = Table.ReplaceValue(
    Source, "N/A", null, Replacer.ReplaceValue, {"Amount"}
),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value", {{"Amount", type number}})

Cause 4: Hidden Whitespace or Non-Breaking Spaces

Data that's passed through a PDF export, a copy-paste from a web page, or certain legacy systems can carry non-breaking space characters or trailing whitespace that look identical to a normal space — or nothing at all — but aren't.

"1234 "   (trailing space, invisible)
"1234"    (clean)

Look identical. Only one converts.

Fix: Text.Trim before the type conversion — but note plain Text.Trim doesn't always catch a non-breaking space (Unicode 00A0), which sometimes needs an explicit replace first.

#"Cleaned" = Table.TransformColumns(
    Source, {{"Amount", each Text.Trim(Text.Replace(_, "#(00A0)", " "))}}
),
#"Changed Type" = Table.TransformColumnTypes(#"Cleaned", {{"Amount", type number}})

See M Language for more on M's text and type functions.

Common Mistakes

Fixing one bad value without checking for others. Replacing the specific value from the error message feels done, but the same source often has more than one placeholder pattern ("N/A" and "TBD" and a blank) — a single replace fixes only the one that happened to error first.

Trusting a date just because it didn't error. As Cause 2 shows, the silent locale mismatch never produces an error to catch — the only defense is checking the locale explicitly, not waiting for Power Query to complain.

Fixing the symptom in Power Query without asking why the source exports inconsistent formats. If the same source keeps producing this on every refresh, the more durable fix is often upstream — a consistent export format or a documented locale — not a growing pile of Table.ReplaceValue steps.

Originally published on PBIDocs — Power BI documentation covering DAX, Power Query, data modeling, and Microsoft Fabric.

Why Did My Power Query Refresh Suddenly Get Slower?

PBIDocs — Tue, 25 Aug 2026 20:06:00 +0000

There's no error message for this one — the refresh just finishes. It's just slower. A query that used to take ten seconds now takes ten minutes, nothing was obviously changed, and there's nothing in the UI shouting about why.

Last week: refresh in 8 seconds
This week: refresh in 6 minutes
No error. No warning. Nothing "broke" in the way an error implies.

This almost always means query folding stopped somewhere in the query — silently, since a broken fold isn't an error condition, just a much slower execution path.

How to Actually Check

Right-click any step in Applied Steps and look at View Native Query. If it's available, everything up to and including that step is still folding to the source. The moment it's grayed out, folding has already stopped at or before that step.

Source          -> View Native Query available
Filtered Rows   -> View Native Query available
Added Custom    -> View Native Query grayed out   <- folding stopped here
Renamed Columns -> still grayed out (nothing after a break can fold again)

Work backward from the last step, checking each one, until the option is available again — that's the exact boundary where folding broke.

Cause 1: A New Step Was Added in the Wrong Position

The most common cause of a query that "used to be fast." Adding a new step doesn't insert it at the end of a logical plan — it inserts it exactly where Applied Steps shows it, and everything downstream inherits whatever folding state that step leaves behind.

Before:  Source -> Filter (folds) -> Select Columns (folds) -> Changed Type (folds)
After:   Source -> Filter (folds) -> Added Custom Column (doesn't fold) -> Select Columns -> Changed Type
                                            ^
                                   everything from here runs locally now

Fix: move steps that can't fold (custom columns, complex conditional logic) as late in the sequence as possible — after filtering and column selection, not before. See Ordering Steps to Preserve Folding.

Cause 2: A Custom Column With Row-by-Row Logic

Table.AddColumn with an each expression referencing M functions that have no equivalent in the source's native query language can't be translated back — the entire step, and everything after it, has to run locally.

#"Added Custom" = Table.AddColumn(
    Source, "Flag",
    each if Text.Contains([Notes], "urgent") then "Y" else "N"
)

This is often unavoidable — not every transformation has a source-side equivalent — but it's worth knowing it's the trade being made, and placing it as late as possible so it affects the smallest number of rows and downstream steps.

Cause 3: Table.Buffer in the Wrong Spot

Table.Buffer() forces full materialization into memory — useful for stabilizing a volatile source, but it also ends folding immediately at that point, even if every step before and after it would otherwise fold cleanly.

Source (folds) -> Filter (folds) -> Table.Buffer -> Group (doesn't fold, runs locally)

See Table.Buffer — this one is easy to miss specifically because it doesn't look like a transformation at all, just a performance-sounding function name.

Cause 4: A Merge Against a Non-Folding Source

Merging a folding query (say, a SQL table) with a query from a source that can't fold (an Excel file, a CSV, an API call) means the combined result can't be pushed back to a single source's native query language — there's no one engine that understands both halves.

SQL query (folds) + Excel query (never folds)
        |
        merged
        |
Result: doesn't fold, regardless of how well the SQL side folds alone

This is sometimes unavoidable (the data genuinely lives in two different places), but it's worth knowing the merge itself is where folding ends, not something to debug further downstream.

Common Mistakes

Assuming a slow refresh means the source is just slow. It's easy to blame the database or the network before checking whether the query itself stopped folding — check View Native Query before escalating to infrastructure.

Adding steps in whatever order feels natural, not a folding-aware order. Applied Steps records the order things were built, not necessarily the order they should stay in — reordering after the fact is normal and often the entire fix.

Not re-checking folding after adding new steps to a previously-fast query. A query that folded perfectly last month can silently stop folding the moment one new step is added — checking once at the start isn't enough if the query keeps evolving.

Originally published on PBIDocs — Power BI documentation covering DAX, Power Query, data modeling, and Microsoft Fabric.

"Why Did My Power Query Refresh Suddenly Get Slower?"

PBIDocs — Tue, 25 Aug 2026 20:02:12 +0000

Last week: refresh in 8 seconds
This week: refresh in 6 minutes
No error. No warning. Nothing "broke" in the way an error implies.

This almost always means query folding stopped somewhere in the query — silently, since a broken fold isn't an error condition, just a much slower execution path.

How to Actually Check

Source          -> View Native Query available
Filtered Rows   -> View Native Query available
Added Custom    -> View Native Query grayed out   <- folding stopped here
Renamed Columns -> still grayed out (nothing after a break can fold again)

Work backward from the last step, checking each one, until the option is available again — that's the exact boundary where folding broke.

Cause 1: A New Step Was Added in the Wrong Position

Before:  Source -> Filter (folds) -> Select Columns (folds) -> Changed Type (folds)
After:   Source -> Filter (folds) -> Added Custom Column (doesn't fold) -> Select Columns -> Changed Type
                                            ^
                                   everything from here runs locally now

Cause 2: A Custom Column With Row-by-Row Logic

#"Added Custom" = Table.AddColumn(
    Source, "Flag",
    each if Text.Contains([Notes], "urgent") then "Y" else "N"
)

Cause 3: Table.Buffer in the Wrong Spot

Source (folds) -> Filter (folds) -> Table.Buffer -> Group (doesn't fold, runs locally)

See Table.Buffer — this one is easy to miss specifically because it doesn't look like a transformation at all, just a performance-sounding function name.

Cause 4: A Merge Against a Non-Folding Source

SQL query (folds) + Excel query (never folds)
        |
        merged
        |
Result: doesn't fold, regardless of how well the SQL side folds alone

This is sometimes unavoidable (the data genuinely lives in two different places), but it's worth knowing the merge itself is where folding ends, not something to debug further downstream.

Common Mistakes

Originally published on PBIDocs — Power BI documentation covering DAX, Power Query, data modeling, and Microsoft Fabric.

"Power Query Error: Formula.Firewall and Privacy Level Errors"

PBIDocs — Sun, 16 Aug 2026 15:46:19 +0000

This isn't a syntax error or a bug in the query — it's Power Query's Formula Firewall refusing to combine data from more than one source until it knows whether that's actually safe. Combining a private/organizational source with a public one (an internal database and a public web API, for example) can leak data from one into the other; the firewall blocks it by default rather than guessing.

Why This Exists

Every data source in Power Query has a privacy level — Public, Organizational, or Private — set the first time it's connected to. When a query's steps end up needing to send data from one source into a call against a different source, Power Query checks whether the privacy levels involved allow that combination.

Originally published on PBIDocs — Power BI documentation covering DAX, Power Query, data modeling, and Microsoft Fabric.

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

PBIDocs — Sun, 16 Aug 2026 15:45:17 +0000

description: "The key didn't match any rows in the table" comes from LOOKUPVALUE finding zero matches, not too many. Here's how to find why, and the four usual causes.
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]
)

If some ProductKey in FactSales has no matching row in DimProduct, this measure throws that exact error for those rows.

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      <- catches orphaned keys

See Dimension Tables for why dimension tables are built to be the complete, authoritative list a fact table can always join against.

Cause 4: A Default Value Was Never Set

Independent of fixing the underlying data, LOOKUPVALUE() has an optional final argument specifically for this situation — a value to return instead of erroring when nothing matches.

Product Category =
LOOKUPVALUE(
    DimProduct[Category],
    DimProduct[ProductKey], FactSales[ProductKey],
    "Unknown"
)

This doesn't fix why the key is missing, but it stops one bad row from breaking the entire calculation while the underlying cause gets investigated. See LOOKUPVALUE for the full syntax.

Common Mistakes

Adding a default value without investigating why. LOOKUPVALUE(..., "Unknown") makes the error go away, but if the missing keys represent a real data quality problem (like Cause 3), silently defaulting to "Unknown" can hide it indefinitely instead of fixing it.

Assuming it's always a relationship problem. This specific error comes from LOOKUPVALUE() and similar functions evaluated directly — not from a standard modeled relationship, which handles unmatched keys differently (rows just don't appear, or appear under a blank member, rather than throwing this error). Don't go looking in Model view for a broken relationship when the actual cause is a DAX formula.

Fixing the symptom on only one side. A type or whitespace mismatch usually needs fixing wherever the bad data originates, not just patching the lookup formula — otherwise the same mismatch reappears the next time new data loads.

Checklist

The specific missing keys have been identified (via a membership check), not just guessed at.
Data types match exactly between the two columns being compared.
Text keys are trimmed and case-normalized before being used as lookup keys.
If the value is legitimately absent from the dimension table, that's addressed as a modeling decision (keep historical rows, or add a placeholder), not just papered over with a default.

Originally published on PBIDocs — Power BI documentation covering DAX, Power Query, data modeling, and Microsoft Fabric.

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

PBIDocs — Sat, 15 Aug 2026 22:14:50 +0000

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

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 <- catches orphaned keys
See Dimension Tables for why dimension tables are built to be the complete, authoritative list a fact table can always join against.

Cause 4: A Default Value Was Never Set
Independent of fixing the underlying data, LOOKUPVALUE() has an optional final argument specifically for this situation — a value to return instead of erroring when nothing matches.

Product Category =
LOOKUPVALUE(
DimProduct[Category],
DimProduct[ProductKey], FactSales[ProductKey],
"Unknown"
)
This doesn't fix why the key is missing, but it stops one bad row from breaking the entire calculation while the underlying cause gets investigated. See LOOKUPVALUE for the full syntax.

Common Mistakes
Adding a default value without investigating why. LOOKUPVALUE(..., "Unknown") makes the error go away, but if the missing keys represent a real data quality problem (like Cause 3), silently defaulting to "Unknown" can hide it indefinitely instead of fixing it.

Assuming it's always a relationship problem. This specific error comes from LOOKUPVALUE() and similar functions evaluated directly — not from a standard modeled relationship, which handles unmatched keys differently (rows just don't appear, or appear under a blank member, rather than throwing this error). Don't go looking in Model view for a broken relationship when the actual cause is a DAX formula.

Fixing the symptom on only one side. A type or whitespace mismatch usually needs fixing wherever the bad data originates, not just patching the lookup formula — otherwise the same mismatch reappears the next time new data loads.

Checklist
The specific missing keys have been identified (via a membership check), not just guessed at.
Data types match exactly between the two columns being compared.
Text keys are trimmed and case-normalized before being used as lookup keys.
If the value is legitimately absent from the dimension table, that's addressed as a modeling decision (keep historical rows, or add a placeholder), not just papered over with a default.
Originally published on PBIDocs — Power BI documentation covering DAX, Power Query, data modeling, and Microsoft Fabric.