How to Fix Power Query Cannot Convert Values to Numbers in Excel
Question details
Users encounter errors when attempting to convert a column of seemingly numeric data into a Number data type in Power Query.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Changing the data type of a column to Number or Whole Number in the Power Query Editor.
- Observed behavior
- Certain rows fail to convert and return an 'Error' value instead of numbers, typically caused by hidden formatting or non-printing characters.
Ensure you click 'Keep Errors' temporarily to isolate the exact rows causing the issue before applying bulk transformations.
Apply Trim and Clean Transformations
Remove invisible non-printing characters and leading or trailing spaces that prevent numbers from being recognized by Power Query.
Often, data imported from web pages or external databases contains hidden line breaks, leading spaces, or trailing spaces. Power Query cannot parse these as numbers until the text is sanitized.
Click on the header of the column containing the values that failed to convert in the Power Query Editor.
Navigate to the 'Transform' tab on the top ribbon, click on 'Format', and select 'Trim' from the dropdown to remove normal leading and trailing spaces.
Go back to the 'Format' menu under the 'Transform' tab and select 'Clean' to strip out any non-printing or control characters from the dataset.
Click the data type icon (usually reading 'ABC' or 'Any') next to the column header and select 'Whole Number' or 'Decimal Number'.
Identify Unexpected Characters using Excel Formulas
Investigate specific error-producing values by copying them to a standard spreadsheet to reveal hidden ASCII characters like non-breaking spaces.
Process Data Smoothly with WPS Spreadsheet
If you frequently struggle with complex data import errors in Microsoft Excel's Power Query, try WPS Office. It provides a lightweight, highly compatible, and user-friendly spreadsheet environment equipped with intuitive data cleaning tools.
- 1. Download and install: Get WPS Office for free from the official website and run the quick installer.
- 2. Open your dataset: Launch WPS Spreadsheet and open your existing Excel or CSV file directly.
- 3. Clean your data: Use the built-in 'Data' tab tools or functions like =TRIM() and =CLEAN() to easily sanitize your numbers without complex query editors.
Frequently Asked Questions
Why does Power Query show an error when converting text to numbers?
This typically happens because the text contains characters that are not recognizable digits, such as letters, currency symbols, leading/trailing spaces, or hidden non-printing characters like line breaks.
What does the Clean function in Power Query actually do?
The Clean function removes non-printing control characters from your text. These are often hidden characters exported from databases or web applications that interfere with data type conversions.
How do I remove non-breaking spaces (character code 160) in Power Query?
The standard Trim function does not always remove non-breaking spaces. To remove them, use the 'Replace Values' feature in the Transform tab. Enter '#(00A0)' (the hex code for a non-breaking space) in 'Value To Find', leave 'Replace With' blank, and apply.
Can I simply ignore or remove the errors when changing data types?
Yes, you can right-click the column header and select 'Remove Errors', or go to Home > Remove Rows > Remove Errors. However, this deletes the entire row from your dataset, which can lead to data loss. Cleaning the data is generally preferred over removing errors.
