DataFormat.Error: Unable to Convert Value to Number in Data Processing
This article focuses on resolving the issue of ‘DataFormat.Error: We couldn’t convert to Number‘. Utilizing Power Query in Excel or Power BI enhances data analysis and reporting, but users may encounter frequent challenges related to data type conversion, particularly when dealing with inconsistent formats.
Whether it’s a result of incorrect delimiters, a mix of data types, or issues related to regional settings, these factors can mislead Power Query into misinterpreting the data. This misinterpretation leads to a conversion error stated as:
DataFormat.Error: We couldn’t convert to Number. Details: [error_details]
In this guide, we will delve into the common causes of the error and offer effective solutions for troubleshooting.
Resolving DataFormat.Error: We couldn’t convert to Number
To address the DataFormat.Error: We couldn’t convert to Number issue in Power Query for Excel or Power BI, consider the following methods:
- Remove the automatic ‘Change Type’ step
- Eliminate non-numeric values
- Standardize and cleanse data prior to conversion
- Set delimiters and locale manually
- Adjust decimal and thousand separators accordingly
Let’s look into each solution in detail.
1] Remove the automatic ‘Change Type’ step
Power Query frequently adds a ‘Change Type’ step automatically upon data import, attempting to assign data types based on a sample of the first 1,000 rows. If your dataset contains mixed types, such as numbers and text within the same column, this automatic process can trigger the ‘DataFormat.Error: We couldn’t convert to Number’ message.
To resolve this, navigate to the Data tab (in Excel) and select Get Data > Launch Power Query Editor (or click Transform Data in Power BI).
Within the Power Query Editor window’s right sidebar, you’ll find the Applied Steps section. If a ‘Changed Type‘ step appears, it indicates automatic type assignment. Click the X next to this step to remove it, preventing further automatic conversions by Power Query.
After clearing the ‘Changed Type’ step, manually specify the data type for each column to ensure correctness.
2] Eliminate non-numeric values
Columns that combine text and numeric entries (such as “ABC 123”) can lead Power Query to attempt a number conversion across the board, which results in the aforementioned error. To rectify this, you can:
- Change the column’s data type to ‘Text’, use the Replace Values functionality to amend the erroneous entries, and subsequently revert the data type to ‘Decimal Number’.
- Employ the Split Column feature to divide the data by a specific delimiter, applying the ‘Number’ type to the numeric segment and ‘Text’ to the other.
- Filter out rows containing non-numeric entries that should not be present before converting the column to a Number format.
Once you have filtered out the non-numeric entries, altering the original column’s data type should proceed without issue.
3] Standardize and cleanse data before type conversion
Data retrieved from websites or various sources might contain non-breaking spaces or symbols such as ‘&’ and ‘,’ that complicate Power Query’s attempt to convert or merge columns.
Utilize the CLEAN and TRIM functions available in the Transform menu to eliminate these unwanted characters (the TRIM function eliminates excess spaces, while CLEAN removes all non-printable characters, which is particularly useful for cleaning external data imports).
After applying these functions, try to set the column’s data type once more.
4] Manually set delimiters and locale
If your data involves specific regional formats, ensuring Power Query operates with the correct delimiter and locale is essential. For instance, if handling European data that uses commas for decimal points, manually adjust the locale settings by navigating to File > Options > Query Options > Regional Settings and changing the Locale option to reflect the data source accurately.
For a single column, you can use the Change Type > Using Locale option to facilitate the proper conversion with the appropriate regional settings applied.
Moreover, while dealing with CSV files, verify whether the data uses commas or semicolons for delimiters and adjust these settings accordingly on import.
5] Adjust decimal and thousand separators accordingly
Finally, if Power Query struggles with number conversions due to inconsistent decimal and thousand separators, utilize the Replace Values feature to correct the mismatched separators (for example, swap ‘.’ with ‘,’ to align with European formatting).
That concludes our troubleshooting tips!
How to resolve the DataFormat error: we couldn’t convert to number?
Ensure that the columns you are dealing with are accurately formatted either as ‘Text’ or ‘Number’. When encountering the ‘DataFormat.Error: We couldn’t convert to Number’, check the error message’s ‘Details’ section, which often highlights the particular entry or character that Power Query cannot process as a number. If both text and numeric types exist in one column, consider splitting it into separate columns and then assigning the appropriate data type to the numeric column.
Why is Excel preventing me from converting to number?
Excel may stop you from converting text-stored numbers to numeric format if the cells are formatted as ‘Text’ or contain hidden characters like non-breaking spaces or special symbols. Additionally, if any affected cells are merged, Excel can restrict certain functions, including number conversion. Be sure to unmerge those cells before attempting any conversions.
Leave a Reply