Fix the Excel Power Query DataFormat Error (M Code Bypass)

An excel power query dataformat error occurs when the engine attempts to force a specific data type (like a Number or Date) onto a cell containing incompatible characters, crashing the data refresh entirely. To bypass this, delete the auto-generated Changed Type step and cast the column manually using a safe try ... otherwise expression in the M language.

Add a custom column with this exact formula:

Code snippet

= Table.AddColumn(Source, "Clean_Revenue", each try Number.From([Revenue]) otherwise null, type number)

This architectural logic intercepts the parsing failure before it hits the grid. Instead of halting the query, the engine quietly converts the offending text into a null value, allowing the rest of your automated dataset to load without interruption.

Prerequisites

• Excel 365 or Excel 2021: You must process your data through the modern Power Query Editor interface (Data > Get Data).
• Formula Bar Visibility: Ensure you have the formula bar active (View > Formula Bar inside the Power Query Editor) to edit M code steps directly.
• Removed Auto-Typing: You must physically delete the hardcoded Changed Type step from your “Applied Steps” pane before executing the custom column bypass.

The Syntax Breakdown

This fix shifts control from Excel’s fragile automated profiling algorithm directly to your own error-handling logic.

• Table.AddColumn(...) — Initializes a new column to hold the safely casted data. This preserves the original raw column for downstream data quality audits.
• each — The M language iteration engine. It forces the query to execute the subsequent logic iteratively against every single row in the dataset.
• try — The error-isolation mechanism. It tests the expression immediately following it. If the expression evaluates successfully, it returns the result. If it triggers a crash, it catches the exception internally.
• Number.From([Revenue]) — The explicit conversion function. It attempts to parse the raw text inside the [Revenue] column into a strict numerical value. (You can substitute Date.From for date-based columns).
• otherwise null — The fallback condition. If the try statement fails, the engine outputs an empty null value instead of returning a fatal error string to the calculation thread.
• type number — Defines the final schema of the new column, signaling to the data model that the resulting array is exclusively numeric.

Real-World Example: Consolidating Regional Sales Exports

An operations manager aggregates daily CSV sales exports from five regional franchises using a master Power Query folder connection. The query works flawlessly for months.

On Tuesday, the London office uploads a file where a staff member typed “TBD” into the Revenue column instead of leaving it blank. During the overnight refresh, Power Query evaluates the “TBD” string against its auto-generated Table.TransformColumnTypes step. The entire ETL pipeline crashes, throwing DataFormat.Error: We couldn't convert to Number, and the morning executive dashboard breaks.

Raw Data Table (London Export)

Order ID	Region	Revenue (Raw Text)
ORD-881	EMEA	4500
ORD-882	EMEA	3200
ORD-883	EMEA	TBD
ORD-884	EMEA	1950

The Formula / Script Apply

The manager opens the Power Query Advanced Editor, deletes the fragile Changed Type step, and applies the protective casting logic:

Code snippet

#"Added Safe Revenue" = Table.AddColumn(#"Promoted Headers", "Safe_Revenue", each try Number.From([Revenue]) otherwise null, type number),
#"Removed Original" = Table.RemoveColumns(#"Added Safe Revenue",{"Revenue"})

Output Table

Order ID	Region	Safe_Revenue (Numeric)
ORD-881	EMEA	4500
ORD-882	EMEA	3200
ORD-883	EMEA	null
ORD-884	EMEA	1950

The dashboard refresh now completes cleanly. The manager can safely aggregate the data, while the null value prevents the mathematical calculations from failing.

Common Related Errors & Fixes

• “We couldn’t parse the input provided as a Date value” -> Cause: You are importing European dates (DD/MM/YYYY) into a US-configured Excel instance (MM/DD/YYYY). The engine cannot decipher month 25. -> Fix: Do not use try...otherwise. Pass a cultural locale parameter to the type transformation step to force correct parsing: Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB").
• “The column ‘XYZ’ of the table wasn’t found” -> Cause: Your auto-generated Changed Type step hardcoded the exact column names of a previous file. When a new file arrives with a slightly altered header, the hardcoded step fails. -> Fix: Delete the Changed Type step entirely from your Applied Steps pane and let the data load as un-typed text, or build a dynamic column referencing script.

Key Takeaways

• Power Query only profiles the first 200 rows of a file. Relying on the automatic Changed Type step is the leading cause of sudden refresh failures in production environments.
• Wrapping type conversions in try ... otherwise null guarantees uninterrupted ETL refreshes by isolating and dropping dirty data organically.
• Always preserve the original raw column during transformation to audit data quality later, dropping it only at the final stage of your query.

Frequently Asked Questions

Can I replace the error with a zero instead of null?

Yes. You can modify the fallback condition in your M code to read otherwise 0. Replacing missing values with a hard zero will artificially skew your statistical averages and aggregation math, whereas most native Excel functions safely ignore a null value.

Why does the error only appear when I load the data to the worksheet?

The Power Query Editor preview pane only evaluates a subset of your data (usually the top 1,000 rows) to conserve local memory. If the corrupted cell exists at row 15,000, the editor displays a green success bar, but the final execution crashes when the engine materializes the full dataset.

How do I find exactly which row caused the crash?

Before applying the structural fix, navigate to the Add Column ribbon tab and select Index Column. Right-click the column header containing the error, select Keep Errors, and the engine will filter the grid to show only the corrupted rows alongside their exact index position.