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.
