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

The full error usually reads:

DataFormat.Error: We couldn't convert to Number.
Details:
    1.234,56

or the date equivalent:

DataFormat.Error: We couldn't parse the input provided as a Date.

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

Try a plain integer like 1234 — no separators at all means no ambiguity, so both locales agree. Then try 1,234 on its own — even a single comma is ambiguous between "one thousand two hundred thirty-four" and "one point two three four" depending on locale.

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 is actually the easier case: no month goes past 12, so a month-first locale like en-US rejects it outright — 13 isn't a valid month, and the conversion errors loudly instead of guessing. A day-first locale like en-GB parses the same text fine, as April 13th. Either way, there's a clear signal something needs checking. 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.

Try 13/04/2026 — only one locale can parse it, and the other fails loudly instead of guessing. Then try 03/04/2026 again to see the silent, both-succeed case.

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.

Next Steps

• M Language
• Transformations
• Power Query Editor
• Table.TransformColumnTypes() — the full syntax behind the locale fix above