Power QueryExercises

Fix DataFormat.Error at the source

Find the raw values behind a conversion error and choose a defensible fix.

~10 min read · intermediate · Module 20: Projects, debugging and interviews

By Tahir Nas · Examples run in our simulator · Updated

Before this: Debug a broken query step by step, try … otherwise

DataFormat.Error means a value couldn't be read as the type you asked for. In a column it sits quietly in one cell; the query fails only when something needs that value, such as a total. That's why the error often appears far from its cause.

The symptom

let
    Source = #table({"Line", "Qty"}, {{1, "4"}, {2, "N/A"}, {3, "12 units"}, {4, "6"}}),
    Typed = Table.TransformColumnTypes(Source, {{"Qty", type number}}),
    Total = List.Sum(Typed[Qty])
in
    Total

Result: an error

DataFormat.Error: We couldn't convert to Number.

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

See the raw values

After the type change, the error cells no longer show what the text was. Keep a raw copy before converting, then list the rows whose converted value is an error:

let
    Source = #table({"Line", "Qty"}, {{1, "4"}, {2, "N/A"}, {3, "12 units"}, {4, "6"}}),
    WithRaw = Table.DuplicateColumn(Source, "Qty", "Qty raw"),
    Typed = Table.TransformColumnTypes(WithRaw, {{"Qty", type number}}),
    Bad = Table.SelectRowsWithErrors(Typed, {"Qty"})
in
    Table.SelectColumns(Bad, {"Line", "Qty raw"})

Result

LineQty raw
2N/A
312 units

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Two different problems: N/A means 'unknown', and 12 units is a real quantity with a unit attached. They deserve different fixes.

Fix each cause deliberately

let
    Source = #table({"Line", "Qty"}, {{1, "4"}, {2, "N/A"}, {3, "12 units"}, {4, "6"}}),
    Cleaned = Table.TransformColumns(Source, {{"Qty", each if _ = "N/A" then null else Text.Select(_, {"0".."9", "."}), type text}}),
    Typed = Table.TransformColumnTypes(Cleaned, {{"Qty", type number}}),
    Total = List.Sum(Typed[Qty])
in
    Total

Result

22

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

N/A becomes null (unknown, excluded from the sum); "12 units" keeps its digits.

Text.Select keeps only the listed characters, so a unit suffix disappears. It would also turn "1,200" into "1200" and "-5" into "5", so use it only when you've seen the values and know that's safe. Report what you changed: 'line 2 had no quantity; line 3 had a unit suffix'.

When the culture is the cause

If many values fail and they look like valid numbers ("1.234,56") or dates ("28/02/2026"), the conversion is using the wrong culture. Fix the culture, not the values: see locale conversion.

Where to find this in Excel

Excel for Windows
Select the column > Home > Keep Rows > Keep Errors
What you see
Only the rows whose conversion failed remain, so you can read the bad values before fixing them.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Dealing with errors in Power Query (Microsoft Learn).

Common mistakes

  • Replacing every error with 0 so the total works, inventing quantities.
  • Stripping characters from every value without checking signs, separators and decimals.
  • Treating a culture problem as dirty data and cleaning thousands of valid values.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: click an Error cell to see DataFormat.Error: We couldn't convert to Number. with the offending value in the details.
  • Excel's load summary shows the number of errors per query; clicking it creates a query listing the rows.
  • The simulator raises DataFormat.Error for failed conversions; currency and percent text such as "$5" also fail here pending native verification (compatibility page KD16).
Functions in this lesson:Table.DuplicateColumnTable.SelectRowsWithErrorsText.SelectList.Sum

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Debugging and interview quiz