DataFormat.Error: We couldn't convert to Number
By Tahir Nas · Examples run in our simulator · Published · Updated
DataFormat.Error: We couldn't convert to Number.
Text that isn't a number was converted to a number type, such as "n/a", an empty string or a decimal comma read with the wrong culture.
Common causes
- Placeholder text such as n/a, TBC or -.
- Empty strings in a numeric column.
- Locale mismatch: 1.234,56 read as en-US.
- Currency symbols or thousands separators the culture doesn't expect.
How to fix it
- Convert with the source culture: Table.TransformColumnTypes(…, "de-DE").
- Replace or remove errors after the type change.
- Use try Number.FromText(x) otherwise null in a custom column.
Find the step that fails
- The error appears in the cells, not as a query failure: in the editor, use View → Column quality to see the error count, then Keep Errors on that column to list the offending rows.
- In Excel, a loaded table may show the rows as errors only in the Queries pane's error count; open the query to see them.
Minimal example
Raises the error:
Number.FromText("n/a")Fixed:
try Number.FromText("n/a") otherwise nullBoth snippets are run by the Power Query Exercises simulator on every build: the first must raise DataFormat.Error, the second must run.
Practise avoiding it
- intermediate~8 minFree
Convert day-first dates with the right locale (consultancy)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- intermediate~7 minFree
Read amounts written with a decimal comma (consultancy)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.
- beginner~7 minPro
Remove rows whose quantity isn't a number (kite shop)
Typing Qty as a whole number turns placeholders like n/a into errors. Remove those rows.
- intermediate~8 minPro
Replace bad quantities with null and flag them (kite shop)
Keep every order, but turn unreadable quantities into null and add a Needs Review flag.
Guides
- Change type using locale: dates and numbers from other countries
- Power Query data type pitfalls that quietly change your numbers
- Why is my filter keeping blanks? null, empty text and errors in practice
- Power Query error messages: a triage map