This submit explains how to repair ‘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. Particulars: [error_details]
On this submit, we’ll discover the foundation causes behind the error and supply sensible options to troubleshoot the difficulty.
Repair DataFormat.Error: We couldn’t convert to Number
To repair DataFormat.Error, We couldn’t convert to Number, when working with information in Energy Question, whether or not in Excel or Energy BI use these options:
- Delete the automated ‘Change Type’ step
- Filter out non-numeric values
- Clear and standardize information earlier than kind conversion
- Manually set delimiters and locale
- Right decimal and thousand separators
Allow us to see this intimately.
1] Delete the automated ‘Change Type’ step
Energy Question usually robotically provides a ‘Change Type’ step after loading information to try to assign information varieties to columns based mostly on the primary 1,000 rows. In case your information consists of inconsistent varieties, resembling numbers combined with textual content in the identical column, this computerized step could cause ‘DataFormat.Error: We couldn’t convert to Number’.
To repair this, go to the Knowledge tab (in Excel), and choose Get Knowledge > Launch Energy Question Editor (in Energy BI, click on Remodel Knowledge).
On the right-hand aspect of the Energy Question Editor window, you’ll see the Utilized Steps pane. If there’s a step referred to as ‘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 information kind of every column to guarantee it’s right.
2] Filter out non-numeric values
Columns containing each textual content and numeric values (e.g., “ABC 123”) could cause Energy Question to try to convert every thing to a quantity, main to the above error. You could do a few issues to repair this:
- Change the information kind of the column to ‘Text’, use the Change Values choice to right the problematic entry, after which revert the information kind to ‘Decimal Number’.
- Use the Break up Column characteristic to cut up the information by a delimiter. Then apply the ‘Number’ information kind to the numeric column and the ‘Text’ kind to the opposite.
- You may also filter out rows with non-numeric values (that shouldn’t be there) earlier than changing the column to the Number information kind.
After filtering out non-numeric values, you possibly can safely change the unique column’s information kind.
3] Clear and standardize information earlier than kind conversion
Knowledge copied from web sites or different sources might embody non-breaking areas or symbols like ‘&’ or ‘,’ that confuse Energy Question whereas making an attempt to convert or merge columns.
Apply the CLEAN or TRIM operate from the Remodel menu to take away these characters (the TRIM operate removes additional areas from textual content, leaving solely single areas between phrases, the CLEAN operate removes all non-printable characters and is especially helpful for cleansing information imported from exterior sources the place hidden characters might exist).
After making use of these capabilities, attempt setting the column’s information kind once more.
4] Manually set delimiters and locale
In case your information entails regional settings, be certain that Energy Question makes use of the proper delimiter and locale. For instance, in case you are working with European information the place commas are used as decimal separators, manually set the information’s locale by going to File > Choices > Question Choices > Regional Settings and adjusting the Locale to match the information supply.
If it’s a single column, use the Change Sort > Utilizing Locale choice to guarantee correct information kind conversion with the proper regional settings utilized.
Equally, for CSV information, examine whether or not the information makes use of commas or semicolons as delimiters and manually modify this throughout import.
5] Right decimal and thousand separators
Lastly, if Energy Question is failing to convert numbers due to mismatched decimal factors and thousand separators, use the Change Values choice to swap the unsuitable separator (e.g., change ‘.’ to ‘,’ for European information) to match the anticipated format.
That’s all!
Learn: How to cut up Knowledge into Rows utilizing Energy Question.
How to resolve DataFormat error we couldn’t convert to quantity?
Be sure that the columns you’re working with are appropriately formatted as ‘Text’ or as ‘Number’. While you encounter ‘DataFormat.Error: We couldn’t convert to Number’, have a look at the ‘Details’ part of the error message. This usually highlights the worth or specific character that Energy Question can’t interpret as a quantity. If a column comprises each textual content and numbers, cut up it into separate columns after which apply the proper information kind to the numeric column.
Why is excel not permitting me to convert to quantity?
Excel won’t permit you to convert numbers saved as textual content to numbers if the cells are formatted as ‘Text’ or in the event that they include invisible characters like non-breaking areas or particular symbols. If any of the affected cells are merged, Excel would possibly limit sure operations like quantity conversion. Unmerge the cells earlier than making an attempt to convert.
Learn Subsequent: Energy BI Unable to join, We encountered an error whereas making an attempt to join.
