You see a “DataFormat.Error: We couldn’t convert to Number” message in Power Query. This happens when Excel’s automatic data type detection misinterprets your data. The feature guesses column types based on the first few rows, which can cause errors with mixed or inconsistent data. This article explains how to disable this detection and manually control your data types.
Key Takeaways: Stop Power Query Type Errors
- Data Type Detection in Power Query Editor: Disable the automatic setting to prevent Excel from guessing column formats incorrectly.
- Transform > Detect Data Type command: Remove this applied step from your query to revert to the original text format.
- Change Type Using Column Header Dropdown: Manually set a column to Text type to preserve leading zeros and mixed content.
Why Power Query Guesses Data Types Incorrectly
Power Query has a default feature called “Data Type Detection.” When you connect to a data source like a CSV file or a database, it scans the first 200 rows of each column. It looks for patterns to assign a data type like Whole Number, Decimal Number, Date, or Text.
This automation fails with inconsistent data. A column may have text identifiers like “ID-001” in later rows, but if the first 200 rows are only numbers, Power Query sets the type to Number. Any subsequent non-numeric row causes a type conversion error. Similarly, dates written in different regional formats or numbers with leading zeros are often changed incorrectly.
The Role of the “Changed Type” Step
Every time Power Query detects a data type, it adds an applied step named “Changed Type” to the query. This step holds the transformation rules. If the detection is wrong, this step is the direct cause of your error. You can view all applied steps in the Query Settings pane on the right side of the Power Query Editor.
Steps to Disable Automatic Data Type Detection
You can turn off the automatic detection globally for new queries or fix an existing query manually. Use the method that matches your situation.
Method 1: Change the Global Setting for New Queries
This setting prevents automatic detection for all future data imports. It does not affect queries you have already created.
- Open the Power Query Editor
In Excel, go to the Data tab. Click Get Data, then launch the Power Query Editor window for any data source. - Access Global Options
Click File > Options and Settings > Query Options. A dialog box will open. - Disable the Detection Setting
In the Query Options dialog, select Global from the left pane. Under the Data Load section, find the setting “Automatically detect column types and headers for unstructured sources.” Uncheck the box next to it. Click OK to save.
Method 2: Manually Fix an Existing Query
For a query that is already throwing errors, you need to edit its applied steps and set the correct data type.
- Locate the Faulty Query
Go to Data > Queries & Connections. In the pane that opens, right-click the query causing the error and select Edit. - Remove the “Changed Type” Step
In the Power Query Editor, look at the Applied Steps list on the right. Find the step named “Changed Type.” Click the X icon to its left to delete it. This reverts the column to its original type, usually Text. - Manually Set the Correct Data Type
Click the header of the column you need to fix. Go to the Transform tab. In the Data Type group, click the dropdown arrow. Select Text to keep all values as text and prevent conversion errors. Alternatively, choose the specific type you need, like Whole Number. - Close and Load the Query
Click Close & Load on the Home tab to apply your changes and refresh the data in your worksheet.
Common Power Query Data Type Scenarios and Fixes
“We Couldn’t Convert to Number” on a Product Code Column
This error appears when a column contains alphanumeric codes like “A100”. Power Query may see the initial numeric part and set the type to Number. The fix is to manually set the column type to Text. This preserves letters, hyphens, and leading zeros.
Dates Appear as Random Numbers or Text
If your source has dates in a format like DD/MM/YYYY, but your system uses MM/DD/YYYY, automatic detection can fail. After removing the “Changed Type” step, use the Transform > Data Type > Date dropdown. If the dates are still wrong, use the Transform > Parse > Date feature with the correct locale setting.
Leading Zeros Are Stripped from ZIP Codes or IDs
Numbers like 00123 get converted to 123 when the column type is set to Whole Number. To keep the leading zeros, you must set the column type to Text before loading the data. Formatting the cells in the worksheet after loading will not restore the original zeros.
Automatic vs. Manual Data Type Control: Key Differences
| Item | Automatic Detection (Default) | Manual Type Setting |
|---|---|---|
| Control | Power Query decides based on sample rows | You explicitly define each column’s type |
| Speed for Clean Data | Fast, requires no initial setup | Slower, requires user action per column |
| Reliability with Mixed Data | Low, high chance of conversion errors | High, you set the correct type for your use case |
| Preserves Original Format | No, it transforms data to match its guess | Yes, especially when using Text type |
| Best For | Consistent, uniformly formatted source data | Legacy data, human-entered files, codes with letters/numbers |
You can now prevent Power Query from changing your data incorrectly. Disable the global detection setting for new imports or edit existing queries to remove the automatic type step. For advanced control, use the Advanced Editor to write M code that defines data types precisely from the start. This gives you complete authority over how your data is interpreted.