An Excel Power Query DataFormat.Error usually means a value does not match the data type or format Power Query is trying to apply—for example, text in a number column or a date written in an unexpected locale. Find the step and value causing the mismatch, then correct the source, adjust the type or locale, or handle the error deliberately before loading the query.
What a Power Query data format error means
Power Query reads data through a sequence of transformation steps. A step may assign a type such as whole number, decimal number, date, or text. If a value cannot be interpreted as that type, Power Query returns an error rather than silently inventing a value. A common message is “DataFormat.Error: We couldn’t convert to Number,” but the wording varies with the operation and source.
The error is different from an ordinary blank. A blank can often become a null value, while an error records that Power Query tried an operation and could not complete it. It is also different from an Excel worksheet error such as #VALUE!: an imported worksheet error may itself become an error in the query, but errors can also arise from text, dates, or other values that were not errors in the source workbook.
Power Query transformations are applied in order. If the query first changes a column to a number and then performs calculations, the conversion can fail before those later steps run. The Applied Steps list is therefore central to diagnosing the issue; the red error is a symptom, and an earlier step may be its cause.
Find the step and value that fail
In Excel for Microsoft 365 on Windows, open Data > Queries & Connections, right-click the affected query, and choose Edit. In Power Query Editor, inspect the preview and the Applied Steps pane. Selecting steps one at a time shows how the data changes. The first step where errors appear is usually the useful starting point, though an error can also be carried forward from an earlier transformation.
To isolate problem rows, select the relevant column and use Home > Keep Rows > Keep Errors. This filters the preview to rows containing errors in the selected column, making it easier to inspect nearby values and identify patterns. You can also use the column’s filter menu to examine error values where available. After diagnosis, remove the temporary Keep Errors step or move back to the step you need to edit; otherwise, the query will retain only those problem rows.
Select an error cell to view its details. The message may identify an invalid value, a failed conversion, or a problem with the source. Compare the displayed value with the column’s assigned type and look for a special case: a dash used for missing data, a number stored with a currency symbol, a date entered as text, or a worksheet error value. Check more than one failing row; one column can contain several different causes.
Fix the conversion or type step
When a column is meant to contain numbers, first decide what each unusual entry means. Correct the source if a value is simply mistyped. If entries such as “N/A” or “not supplied” represent missing information, replace them with a consistent blank or null in the source, or handle them explicitly in the query. If the column is actually an identifier—such as a product code that can start with zero—set it to text rather than forcing it to a number.
In Power Query Editor, select the column and use Transform > Data Type to choose the intended type. You can also use the type icon in the column header. If an automatically generated Changed Type step is applying the wrong conversion, select that step in Applied Steps and change or delete it, then apply the correct type at the appropriate point. Deleting a step can affect later steps that depend on it, so review the preview after making the change.
For a one-off import where the source contains mixed values, setting the column to Text may preserve the original content and prevent conversion errors. This is not always the right final design: numeric text will not behave as a number in later calculations until it is converted. Choose a type that reflects how the data will be used, not merely the type that makes the warning disappear.
Check date and number locale settings
A value can be valid in one regional format and invalid in another. For example, 03/04/2026 may mean March 4 or April 3, depending on the convention. Decimal and thousands separators also differ: 1.234,56 and 1,234.56 can represent the same quantity in different locales. If Power Query interprets a source using the wrong regional settings, ordinary dates or amounts may cause a DataFormat.Error or, more dangerously, be interpreted as a different valid value.
To set the interpretation explicitly, right-click the column header and choose Change Type > Using Locale. Select the intended data type and the locale that matches the source’s representation, not necessarily the locale of your Windows display. For a text date such as 31/12/2026, choose a locale that recognizes day-month-year order. For decimal values, choose the locale that uses the source’s decimal and grouping separators.
Locale conversion is especially useful when a workbook is shared across regions or when data comes from a CSV, website, or business system. It is worth checking the resulting values, not just whether the errors disappear. A date converted without errors can still be wrong if month and day were swapped.
Common sources of the error
- Mixed values in a column: Most rows contain numbers, but one contains “pending,” a dash, or a note. Decide whether to clean the entry, treat it as missing, or keep the entire field as text.
- Unexpected date format: Text dates may use a different order or separator from the locale Power Query expects. Use Change Type > Using Locale and validate representative dates.
- Currency and punctuation: A currency symbol, spaces, parentheses for negatives, or thousands separators may prevent a direct conversion. Remove or interpret these consistently before assigning a numeric type.
- Excel error values: A source cell containing #N/A or another worksheet error can flow into the query as an error. Correct the source formula or decide how the query should represent that result.
- Unexpected header or footer rows: A title, subtotal, or note included among data rows can be treated as a record and fail conversion. Remove non-data rows or promote the correct headers before converting types.
- Automatic type detection: Power Query may add a Changed Type step when importing sources without a firm schema. A column that looks numeric in the sample can contain text or special values in later rows.
Automatic type detection is helpful for quick imports, but it does not prove every value fits the inferred type. For unstructured sources, detection can be based on an initial sample rather than a guarantee that all future rows will conform. This is why an error may appear after new data is added even though the original preview looked clean.
Remove errors, replace them, or preserve them?
Removing error rows is appropriate only when excluding those records is acceptable. In Power Query Editor, select the column and choose Home > Remove Rows > Remove Errors, or use the column’s context menu where the command is available. This drops rows with errors in the selected column; it does not repair their values. If the records matter for reporting, removing them can quietly distort totals or counts.
Replacing errors can be suitable when there is a clear, defensible replacement. Use Transform > Replace Errors, or right-click the column and choose Replace Errors where available, then supply a value compatible with the column type. For a numeric field, replacing an error with zero is not neutral: it changes the meaning and can affect averages, sums, and decisions. A null may better represent an unknown value, but it also affects downstream calculations and should be documented.
Preserve or investigate errors when they indicate a source-quality problem that should be fixed. For recurring workflows, a useful approach is to separate valid records from exceptions, keep the original value where possible, and report how many records failed. The right choice depends on what the data means; there is no universally safe instruction to remove every error.
Make the fix survive refreshes
A Power Query fix is part of the query’s saved transformation steps, so it normally runs again on refresh. However, removing today’s problematic row does not prevent a new malformed row from arriving tomorrow. If a query is refreshed from a folder or regularly updated file, test it with representative new data and make sure type conversion occurs after steps that remove titles, footers, or irrelevant records.
For repeatable imports, standardize the source format where possible, keep identifiers as text, and apply explicit data types and locales. Avoid relying on one preview sample to represent every future file. When using a replace-errors step, choose a replacement that matches the field’s meaning and consider keeping a separate error or audit query so exceptions remain visible rather than disappearing from the report.
After editing, choose Home > Close & Load to return the query results to Excel, then refresh and check the loaded table. If the query feeds a PivotTable or another query, refresh or review those dependent outputs too. A successful load confirms that the current transformation ran; it does not by itself prove that every converted value has the intended meaning.
Power Query errors versus worksheet errors
Power Query works on data before it is loaded into a worksheet. Its error values belong to the query transformation process, while worksheet formulas calculate in the grid after data is loaded. Fixing a worksheet formula may address the source if Power Query reads that cell, but an error created by a Changed Type step must be fixed in the query. Likewise, wrapping a worksheet formula in IFERROR does not repair a conversion that happens upstream in Power Query.
The editor’s Applied Steps also make query errors repeatable and inspectable: a correction can be saved and applied to each refresh. That differs from manually editing a loaded result, which may be overwritten on the next refresh. Make the correction in the source or query rather than directly changing a value in the output table.
Frequently Asked Questions
Why does Power Query say it cannot convert to a number?
Usually, at least one value in the column is not formatted as the expected number. Inspect error rows for text labels, symbols, blanks represented unusually, or separators that do not match the locale. Confirm that the column is truly numeric rather than an identifier.
Why did the query work before but fail after refresh?
New source rows may contain a value that was absent from the earlier data, or the source file’s layout or regional formatting may have changed. Keep Errors can reveal the new records. Then correct the input or update the query’s conversion and validation steps.
Should I remove errors or replace them with zero?
Neither action is automatically correct. Remove a row only if excluding it is acceptable; use zero only when zero is the real meaning of the missing or invalid value. Otherwise, correct the source, preserve the exception, or use a clearly defined null-handling rule.
How do I stop Power Query from changing a column type automatically?
Review the Changed Type step and set the intended type yourself, or remove that step if it is unsuitable. Excel’s Query Options include type-detection settings for unstructured sources, though labels and availability can vary by build. Check the preview and downstream steps after changing detection behavior.
Can I fix a Power Query DataFormat.Error in Excel for Mac or the web?
The steps here describe Excel for Microsoft 365 on Windows. Power Query editing and data-source support differ across Mac builds, and Excel for the web does not provide the same full editing experience as the Windows desktop app. If the editor or command is unavailable, open the workbook in a supported desktop version and check Microsoft’s current feature support for your build.
Related
- Excel Number Format to Show Values in Thousands
- Excel Number Format: Display Zero Values as Dashes
- Excel Number Format With Leading Zeros: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel DAYS Formula Not Working? Fix Dates and ErrorsIf the end date is in B2 and the start date is in A2, enter =DAYS(B2,A2); the end…
- Excel Not a Number? Understand #NUM! and ISNUMBERIn Excel, “not a number” can refer to the #NUM! error, which means a formula cannot produce a…
- Excel Table Colours: Change, Customize, and Fix ThemExcel table colours come from the table’s style: select a cell in the table, open Table Design, and…
- Circular References in Excel: How to Find and Fix ThemTo find a circular reference in Excel for Microsoft 365 on Windows, select Formulas > Error Checking >…
- Excel #VALUE! Error: Causes and FixesIn Excel, #VALUE! means a formula received a value or argument it cannot use, often text where a…
- Excel VLOOKUP #VALUE! Error: Causes, Checks, and FixesIn Excel for Microsoft 365 on Windows, VLOOKUP most commonly returns #VALUE! when its third argument, col_index_num, is…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.