This post explains how to fix ‘DataFormat.Error: We couldn’t convert to Number‘. Working with data in Power Query, whether in Excel or Power BI, can streamline analysis and reporting. However, data type conversion errors are a common frustration many users face, especially while dealing with inconsistent formats.
Whether mismatched delimiters, mixed data types, or regional settings, the issues can cause Power Query to misinterpret the data, leading to a conversion error that reads:
DataFormat.Error: We couldn’t convert to Number. Details: [error_details]
In this post, we’ll explore the root causes behind the error and provide practical solutions to troubleshoot the issue.
Fix DataFormat.Error: We couldn’t convert to Number
To fix DataFormat.Error, We couldn’t convert to Number, when working with data in Power Query, whether in Excel or Power BI use these solutions:
- Delete the automatic ‘Change Type’ step
- Filter out non-numeric values
- Clean and standardize data before type conversion
- Manually set delimiters and locale
- Correct decimal and thousand separators
Let us see this in detail.
1] Delete the automatic ‘Change Type’ step
Power Query often automatically adds a ‘Change Type’ step after loading data to try and assign data types to columns based on the first 1,000 rows. If your data includes inconsistent types, such as numbers mixed with text in the same column, this automatic step can cause ‘DataFormat.Error: We couldn’t convert to Number’.
To fix this, go to the Data tab (in Excel), and select Get Data > Launch Power Query Editor (in Power BI, click Transform Data).
On the right-hand side of the Power Query Editor window, you’ll see the Applied Steps pane. If there’s a step called ‘Changed Type‘, Power Query has automatically assigned data types. Click the X next to the step to delete it. This will stop Power Query from attempting automatic conversions.
After deleting the ‘Changed Type’ step, manually set the data type of each column to ensure it’s correct.
2] Filter out non-numeric values
Columns containing both text and numeric values (e.g., “ABC 123”) can cause Power Query to attempt to convert everything to a number, leading to the above error. You may do a couple of things to fix this:
- Change the data type of the column to ‘Text’, use the Replace Values option to correct the problematic entry, and then revert the data type to ‘Decimal Number’.
- Use the Split Column feature to split the data by a delimiter. Then apply the ‘Number’ data type to the numeric column and the ‘Text’ type to the other.
- You can also filter out rows with non-numeric values (that shouldn’t be there) before converting the column to the Number data type.
After filtering out non-numeric values, you can safely change the original column’s data type.
3] Clean and standardize data before type conversion
Data copied from websites or other sources may include non-breaking spaces or symbols like ‘&’ or ‘,’ that confuse Power Query while trying to convert or merge columns.
Apply the CLEAN or TRIM function from the Transform menu to remove these characters (the TRIM function removes extra spaces from text, leaving only single spaces between words, the CLEAN function removes all non-printable characters and is particularly useful for cleaning data imported from external sources where hidden characters may exist).
After applying these functions, try setting the column’s data type again.
4] Manually set delimiters and locale
If your data involves regional settings, ensure that Power Query uses the correct delimiter and locale. For example, if you are working with European data where commas are used as decimal separators, manually set the data’s locale by going to File > Options > Query Options > Regional Settings and adjusting the Locale to match the data source.
If it is a single column, use the Change Type > Using Locale option to ensure proper data type conversion with the correct regional settings applied.
Similarly, for CSV files, check whether the data uses commas or semicolons as delimiters and manually adjust this during import.
5] Correct decimal and thousand separators
Lastly, if Power Query is failing to convert numbers due to mismatched decimal points and thousand separators, use the Replace Values option to swap the wrong separator (e.g., change ‘.’ to ‘,’ for European data) to match the expected format.
That’s all!
Read: How to split Data into Rows using Power Query.
How to resolve DataFormat error we couldn’t convert to number?
Ensure that the columns you’re working with are correctly formatted as ‘Text’ or as ‘Number’. When you encounter ‘DataFormat.Error: We couldn’t convert to Number’, look at the ‘Details’ section of the error message. This often highlights the value or particular character that Power Query cannot interpret as a number. If a column contains both text and numbers, split it into separate columns and then apply the correct data type to the numeric column.
Why is excel not allowing me to convert to number?
Excel might not allow you to convert numbers stored as text to numbers if the cells are formatted as ‘Text’ or if they contain invisible characters like non-breaking spaces or special symbols. If any of the affected cells are merged, Excel might restrict certain operations like number conversion. Unmerge the cells before attempting to convert.