Power Query Couldn't Convert to Number Error: Locale and Null Fixes
The Power Query “DataFormat.Error: We couldn’t convert to Number” error means a text value in a numeric column cannot be parsed under the active locale. The usual causes are decimal separators that do not match the locale, placeholder text such as “N/A” or ”-”, and whitespace-only cells. Fix it by converting with an explicit culture and a try ... otherwise null wrapper, then check the result with Keep Errors.
Reading the error
The error text rarely names the column. In the editor, the failing cell shows Error, and clicking it opens a Details pane with a line like Value: N/A. In Power BI Service or Excel, a failed refresh reports the same message with the first offending value.
The error is raised by a type conversion step, usually Changed Type, which calls Table.TransformColumnTypes. Power Query evaluates lazily, and the editor preview only loads the first 1,000 rows. A bad value on row 40,000 therefore passes in the editor and fails during refresh.
To locate the offending rows:
- Add an index column before the failing step (Add Column > Index Column > From 1). This preserves the original row numbers.
- Select the step that raised the error in Applied Steps.
- Select the column, then choose Home > Keep Rows > Keep Errors. This adds
Table.SelectRowsWithErrors. - Click any error cell and read the original value in the Details pane.
Delete the Keep Errors step once the diagnosis is done. Left in place, the query returns only the broken rows.
Common causes
Decimal separators and the locale argument
Number parsing depends on culture. Under en-US, the period is the decimal separator and the comma is the thousands separator. Under de-DE or fr-FR, the roles are reversed.
This matters because the CSV stage often auto-detects types from the first 200 rows only. A file exported from a European system may contain 1.299,50, and en-US cannot parse that as a number. Worse, a value like 12,5 can parse as 125, because the comma is read as a grouping character. That error is silent and shifts totals without failing the refresh.
Number.FromText takes the culture as its second argument:
Number.FromText("1
Frequently asked questions
How do I fix DataFormat.Error: We couldn't convert to Number in Power Query?
Find the failing value with Keep Errors, then change the column type using the correct locale. Wrap conversions in a try expression with otherwise null so placeholders like N/A become nulls instead of errors, and the refresh completes without failures.
Why does Power Query fail to convert 1,5 or 1.234,56 to a number?
Power Query parses text using the file or machine locale, often en-US, where comma is the thousands separator and period is the decimal. European-formatted text then fails or parses wrongly. Pass a culture such as de-DE to Number.FromText or Table.TransformColumnTypes.
How do I find which rows cause the Power Query number conversion error?
Add an index column before the type step, then choose Keep Errors under Keep Rows on the failing column. The remaining rows are the offenders, and clicking an error cell shows the exact original value in the Details pane below.