How to Fix Power Query Cannot Convert Text with Commas to Numbers
Question details
The user is experiencing data conversion errors in Power Query when trying to change a text column into a number format due to conflicting comma thousands separators and period decimal separators.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing a CSV file (e.g., from Microsoft Dynamics) formatted with United States regional settings into a Power Query environment configured with a different local region setting.
- Observed behavior
- Power Query returns conversion errors on the affected columns because the text numbers contain commas and periods that do not match the system's expected regional number format.
Verify the original region or system that generated your CSV file (such as the United States) so you know exactly which formatting rules the data is using.
Use 'Change Type Using Locale' for the Specific Column
This is the safest method as it applies the correct regional formatting to a specific column without altering your global workbook settings.
When importing a CSV file, avoid opening and saving it directly in Excel first, as this can corrupt the source formatting. Instead, import the raw CSV into Power Query and adjust the column type using the locale feature.
Import your CSV data into Power Query. Ensure the source step uses QuoteStyle.Csv and that your headers are promoted.
Right-click the column header of the text data that contains the commas and periods you want to convert to numbers.
Navigate to 'Change Type' in the context menu, then select 'Using Locale...' at the bottom of the list.
In the dialog box that appears, set the Data Type to 'Decimal Number'. Set the Locale to match the region of your source data (for example, 'English (United States)'). Click OK.
Change Global Query Regional Settings
Update the regional settings for the entire current workbook to match the CSV source's locale.
Manage and Format CSV Data Seamlessly in WPS Office
If you frequently encounter frustrating regional format conflicts and conversion errors in Excel's Power Query, consider trying WPS Office. WPS Spreadsheets provides an intuitive Data Import wizard that makes handling different CSV locales, comma separators, and text-to-number conversions straightforward, without requiring complex query configurations.
- 1. Import CSV into WPS Spreadsheets: Open WPS Spreadsheets, go to the 'Data' tab, and select 'Import Data' to open your CSV file.
- 2. Use the Text Import Wizard: Follow the on-screen prompts to easily define your delimiters, specify your thousands and decimal separators, and convert text to numbers instantly.
Frequently Asked Questions
Why do commas cause conversion errors in Power Query?
Commas cause errors if your system's regional settings interpret them differently than the source file. For example, some regions use commas as decimal separators, while US formatting uses them as thousands separators. When these rules conflict, Power Query cannot properly calculate the number, resulting in an error.
Can I fix the CSV in Excel before opening it in Power Query?
It is generally best practice to import the CSV into Power Query without editing the source file. Opening and saving a CSV directly in Excel beforehand can accidentally alter data formats, drop leading zeros, and cause irreversible data loss.
Does the 'Using Locale' option permanently change my computer's system language?
No. The 'Using Locale' option in Power Query only applies the selected region's formatting rules to that specific column conversion step. It leaves your overall system language, regional settings, and global Excel settings completely unchanged.
