Data Conversion Nightmare: Solving the "DataFormat.Error: We Couldn't Convert to Number" Issue

Table of Contents

Working with data in Power Query, whether in Excel or Power BI, offers a streamlined approach to data analysis and report generation. However, encountering data type conversion errors can be a common and frustrating hurdle for many users, particularly when dealing with datasets that have inconsistent formats.

Whether the issue stems from mismatched delimiters, a mix of data types within a column, or regional settings that are not correctly configured, these factors can lead Power Query to misinterpret your data. This misinterpretation frequently manifests as a conversion error, specifically:

DataFormat.Error: We couldn’t convert to Number. Details: [error_details]

This error indicates that Power Query has encountered a problem when attempting to convert a value to a numerical data type. It’s a common stumbling block that can halt your data transformation process. Understanding the underlying causes and knowing how to implement effective solutions are crucial for smooth data manipulation in Power Query. Let’s delve into the common root causes of this error and explore practical solutions to effectively troubleshoot and resolve it.

Fix DataFormat.Error: We Couldn’t Convert to Number¶

To effectively resolve the DataFormat.Error: We Couldn’t Convert to Number issue when working with data in Power Query, whether you are using Excel or Power BI, consider implementing these solutions. These methods are designed to address various underlying causes of the error, from automatic type conversion issues to data inconsistencies.

1. Delete the automatic ‘Change Type’ step
2. Filter out non-numeric values
3. Clean and standardize data before type conversion
4. Manually set delimiters and locale
5. Correct decimal and thousand separators

Let’s explore each of these solutions in detail to understand how they can help you overcome the “DataFormat.Error” and ensure successful data conversion in Power Query.

1] Delete the Automatic ‘Change Type’ Step¶

Power Query is designed to be user-friendly, and as part of this design, it often automatically applies a ‘Change Type’ step immediately after data is loaded. This automatic step attempts to intelligently assign data types to each column based on the examination of the first 1,000 rows of your dataset. While this feature is intended to be helpful, it can sometimes be the source of the “DataFormat.Error: We Couldn’t Convert to Number” error.

The problem arises when your data contains inconsistencies, such as a column that predominantly contains numbers but also includes text entries or special characters. In such cases, the automatic ‘Change Type’ step might incorrectly assume the column should be entirely numeric. When it encounters a non-numeric value, it triggers the conversion error.

To rectify this, you need to disable this automatic type conversion and take manual control over data type assignments.

Steps to Delete the Automatic ‘Change Type’ Step:

1. Access Power Query Editor:

   • In Excel: Navigate to the Data tab in the Excel ribbon. Then, in the “Get & Transform Data” group, select Get Data and then Launch Power Query Editor.
   • In Power BI: In Power BI Desktop, go to the Home tab on the ribbon and click on Transform Data.

2. Locate the ‘Applied Steps’ Pane: Once the Power Query Editor window is open, look to the right-hand side of the interface. You will see a pane labeled Applied Steps. This pane lists all the transformations that Power Query has applied to your data in sequence.

3. Identify and Delete ‘Changed Type’ Step: Within the Applied Steps pane, scroll through the list of steps. Look for a step named ‘Changed Type’. This is the automatic data type conversion step that Power Query often adds. If you find this step, it indicates that Power Query has automatically attempted to assign data types. To delete this step, click on the X icon that is located next to the ‘Changed Type’ step. Clicking ‘X’ will remove this step from the query, preventing Power Query from performing automatic data type conversions.

4. Manually Set Data Types: After deleting the ‘Changed Type’ step, it is crucial to manually specify the correct data type for each column. To do this, select the header of the column you want to modify. Right-click on the column header, navigate to Change Type, and then choose the appropriate data type from the dropdown menu. Ensure you select the data type that accurately reflects the content of the column. For columns intended to contain numbers, choose a number format like ‘Decimal Number’, ‘Whole Number’, etc., as needed. For text columns, select ‘Text’.

By deleting the automatic ‘Changed Type’ step and manually assigning data types, you gain precise control over how Power Query interprets your data. This manual approach is particularly effective in preventing the “DataFormat.Error” when dealing with datasets that have mixed or inconsistent data types within columns.

2] Filter Out Non-Numeric Values¶

Columns that inadvertently contain a mixture of text and numeric values are a frequent cause of the “DataFormat.Error: We Couldn’t Convert to Number” error. For instance, a column intended for numerical data might contain entries like “ABC 123” or include textual descriptions along with numbers. When Power Query attempts to convert such a column to a numeric data type, it encounters these non-numeric entries and triggers the error.

To resolve this, you have several effective strategies you can employ within Power Query:

• Change Data Type to Text, Correct, and Revert:

  1. Initially, change the data type of the problematic column to ‘Text’. This will allow Power Query to treat all values in the column as text, temporarily bypassing the number conversion error.
  2. Utilize the Replace Values feature. Select the column, go to the Home tab or Transform tab in the Power Query Editor ribbon, and find the Replace Values option. Use this to identify and correct the problematic text entries that should ideally be numeric. You can either correct them to valid numbers or replace them with null or empty values if they are erroneous.
  3. After cleaning up the problematic entries, revert the data type of the column back to the desired numeric type, such as ‘Decimal Number’ or ‘Whole Number’. Now, Power Query should be able to successfully convert the column to a numeric data type as the non-numeric values have been addressed.

• Utilize the Split Column Feature:

  1. If the text and numeric parts within the column are consistently separated by a delimiter (like a space, hyphen, etc.), you can use the Split Column feature. Select the column, go to the Home tab or Transform tab, and choose Split Column. Select the delimiter that separates the text from the numbers.
  2. Power Query will split the original column into multiple columns based on the delimiter. You will now have a column containing the numeric part and potentially another with the text part.
  3. Apply the ‘Number’ data type to the column that contains the numeric values. Apply the ‘Text’ data type to the column containing the text, if you wish to keep it. You can then remove the text column if it’s not needed for your analysis.

• Filter Out Rows with Non-Numeric Values:

  1. Before attempting to convert the column to a numeric data type, you can filter out rows that contain non-numeric values in that column. Select the column, click on the filter dropdown arrow in the column header.
  2. Apply a filter to keep only the rows where the values are numeric. The specific filtering method might depend on the nature of your data and the types of non-numeric entries. You could use number filters like “Greater Than or Equal to 0” and “Is less than or equal to 999999999” (or a sufficiently large number range to encompass your expected numeric range) to effectively filter out text. Alternatively, you could use “Text Filters” if you know specific text patterns that indicate non-numeric entries, and filter these out.
  3. After filtering, you can safely change the data type of the original column to a numeric data type. Since the non-numeric rows are now filtered out, Power Query should be able to perform the conversion without encountering the “DataFormat.Error.”

By employing these methods to handle non-numeric values, you can effectively prepare your data for successful numeric conversion in Power Query and resolve the “DataFormat.Error.”

3] Clean and Standardize Data Before Type Conversion¶

Data sourced from various places, such as websites or external files, often contains inconsistencies and unwanted characters that can hinder Power Query’s ability to correctly interpret and convert data, especially to numeric formats. These inconsistencies can include non-breaking spaces, special symbols like ‘&’ or ‘,’, or other extraneous characters that are not immediately visible but can interfere with data conversion processes, including column merging operations.

Before attempting to change data types, especially to numeric types, it is crucial to clean and standardize your data. Power Query provides built-in functions within the Transform menu that are specifically designed for this purpose. The CLEAN and TRIM functions are particularly useful in addressing these issues:

• TRIM Function: The TRIM function is used to remove extra spaces from text strings. It eliminates leading spaces (spaces before the text), trailing spaces (spaces after the text), and reduces multiple spaces between words to single spaces. This is beneficial because extra spaces can sometimes cause Power Query to misinterpret values, especially when trying to convert them to numbers. For example, a number with a trailing space “123 ” might not be recognized as a number until the space is removed.

  To apply the TRIM function:
  1. Select the column you want to clean.
  2. Go to the Transform tab in the Power Query Editor ribbon.
  3. In the “Text Column” group, find and click on the Format dropdown.
  4. From the dropdown menu, select Trim. Power Query will apply the TRIM function to all values in the selected column, removing extra spaces.

• CLEAN Function: The CLEAN function is designed to remove non-printable characters from text. Non-printable characters are control characters and other characters that do not have a visual representation when printed or displayed. These characters are often found in data imported from external sources, especially from older systems or web scraping, and can cause issues with data processing and conversion. The CLEAN function is effective in removing these hidden characters that might be causing the “DataFormat.Error.”

  To apply the CLEAN function:
  1. Select the column you need to clean.
  2. Navigate to the Transform tab in the Power Query Editor ribbon.
  3. In the “Text Column” group, click on the Format dropdown.
  4. Select Clean from the dropdown options. Power Query will apply the CLEAN function to the selected column, removing non-printable characters.

After applying either or both of these functions (depending on the nature of your data and the issues you are facing), you should attempt to set the column’s data type again. By removing leading/trailing spaces and non-printable characters, you significantly improve the chances of Power Query correctly interpreting your data and successfully converting it to the desired data type, thus resolving the “DataFormat.Error.”

4] Manually Set Delimiters and Locale¶

When working with datasets that originate from different regional settings or locales, it is essential to ensure that Power Query correctly interprets the data based on the appropriate regional conventions. Regional settings significantly impact how numbers, dates, and other data types are formatted, particularly concerning delimiters like decimal separators and list separators. Incorrect locale settings can lead to Power Query misinterpreting numbers, especially when it comes to decimal points and thousand separators, which can directly cause the “DataFormat.Error: We Couldn’t Convert to Number” error.

• Setting Locale Globally for Data Sources: If your entire dataset or multiple data sources use a specific regional format, you can adjust the global locale setting in Power Query. This ensures that Power Query interprets all data according to the chosen regional settings.

  To adjust the global locale:
  1. In Power Query Editor, go to the File tab in the ribbon.
  2. Select Options and settings and then click on Query Options.
  3. In the “Query Options” dialog box, navigate to the Regional Settings section.
  4. Here, you can set the Locale to match the regional settings of your data source. For example, if you are working with European data where commas are used as decimal separators, you might select a European locale like “German (Germany)” or “French (France)”.
  5. Click OK to save the changes. This global setting will affect how Power Query interprets data from all subsequent queries and data connections.

• Setting Locale for a Specific Column Conversion: If you need to apply a specific locale setting to a single column during data type conversion, Power Query allows you to do this directly when you change the data type.

  To set locale for a specific column conversion:
  1. Select the column for which you want to set a specific locale.
  2. Right-click on the column header.
  3. Navigate to Change Type in the context menu.
  4. Instead of directly selecting a data type, choose Using Locale… from the Change Type options.
  5. In the “Change Type with Locale” dialog box, select the desired Data Type (e.g., Decimal Number, Whole Number).
  6. Then, choose the appropriate Locale from the dropdown menu that matches the regional settings of the data in that specific column.
  7. Click OK. Power Query will convert the data in the column to the specified data type, using the locale you selected to correctly interpret delimiters and formatting.

• Handling CSV File Delimiters: For CSV (Comma Separated Values) files, the delimiter used to separate fields can vary. While CSV implies comma-separated, some CSV files may use semicolons (;) or other characters as delimiters, especially in different regional settings. If Power Query is incorrectly assuming a comma delimiter when your CSV file uses a semicolon, it will misinterpret the data, potentially leading to conversion errors.

  When importing a CSV file, Power Query usually attempts to detect the delimiter automatically. However, if it guesses incorrectly, or if you know the delimiter is not the default comma:
  1. When importing the CSV file (e.g., using Get Data from Text/CSV), in the import dialog box, look for delimiter options.
  2. You should be able to manually specify the delimiter used in your CSV file (e.g., comma, semicolon, tab, etc.). Ensure you select the correct delimiter that matches your CSV file format.
  3. Preview the data in the dialog to confirm that the data is correctly separated into columns based on the delimiter you specified.
  4. Proceed with loading the data after verifying the delimiter setting.

By carefully managing locale settings and delimiters, both globally and at the column level, you can ensure that Power Query accurately interprets your data according to its regional format. This is crucial for preventing “DataFormat.Error” issues that arise from regional formatting differences and for ensuring data is converted correctly.

5] Correct Decimal and Thousand Separators¶

A common cause of the “DataFormat.Error: We Couldn’t Convert to Number” error is the mismatch between the decimal and thousand separators used in your data and what Power Query expects based on its locale settings. Different regions use different conventions for these separators. For example, in many European countries, a comma (,) is used as the decimal separator, and a period (.) might be used as a thousand separator, which is the reverse of the convention in English-speaking countries.

If your data uses a decimal separator that Power Query is not expecting based on its current locale, it will fail to convert the values to numbers. To address this, you can use the Replace Values option in Power Query to swap the incorrect separators with the correct ones before attempting to change the data type to a number.

Steps to Correct Decimal and Thousand Separators:

1. Identify the Incorrect Separators: First, determine which separators are used in your data and which ones Power Query is misinterpreting. For example, if you are working with European data, you might find that commas are used as decimal separators (e.g., 123,45) and periods as thousand separators (e.g., 1.000.000). If Power Query is set to a locale that expects periods as decimal separators, it will fail to convert “123,45” to a number correctly.

2. Use Replace Values to Swap Separators:

   • Select the column that contains the numbers with incorrect separators.
   • Go to the Home tab or Transform tab in the Power Query Editor ribbon and click on Replace Values.
   • In the “Replace Values” dialog box:
     • In the Value To Find field, enter the incorrect decimal separator (e.g., , if commas are used as decimal separators in your data but should be periods).
     • In the Replace With field, enter the correct decimal separator that Power Query expects (e.g., . for a locale like English (United States)).
     • Click OK. This step will replace all commas with periods in the selected column.

3. Handle Thousand Separators (If Necessary): If your data also uses thousand separators that need to be removed or adjusted, you can perform another Replace Values operation.

   • If thousand separators are causing issues (e.g., periods used as thousand separators in European format, like 1.000.000), you might need to remove them before conversion, especially if Power Query in your locale setting does not expect thousand separators in that format.
   • To remove thousand separators, use Replace Values again:
     • In Value To Find, enter the thousand separator character (e.g., . if periods are used as thousand separators).
     • Leave the Replace With field empty. This will effectively remove the thousand separator characters.
     • Click OK.

4. Change Data Type to Number: After correcting the decimal and thousand separators using Replace Values, you can now attempt to change the data type of the column to a numeric type (e.g., Decimal Number, Whole Number). Since the separators are now in a format that Power Query can correctly interpret based on your locale or the adjustments you’ve made, the conversion should be successful, and the “DataFormat.Error” should be resolved.

By carefully correcting decimal and thousand separators using the Replace Values option, you can effectively preprocess your data to align with Power Query’s expected number formats. This ensures accurate data conversion and resolves issues related to regional number formatting, preventing the “DataFormat.Error.”

How to Resolve DataFormat Error We Couldn’t Convert to Number?¶

To effectively resolve the “DataFormat.Error: We Couldn’t Convert to Number”, it is crucial to systematically investigate and address the potential causes within your Power Query transformations.

Firstly, always ensure that the columns you are working with are correctly formatted. Determine if a column should indeed be treated as ‘Text’ or as a ‘Number’. Misidentifying the intended data type is a primary source of conversion errors.

When you encounter the “DataFormat.Error: We Couldn’t Convert to Number”, pay close attention to the ‘Details’ section of the error message. This section often provides valuable clues by highlighting the specific value or character that Power Query is unable to interpret as a number. This direct feedback can pinpoint the exact problematic entry causing the error, allowing for targeted correction.

If a column is found to contain a mix of both text and numbers, a common scenario in messy datasets, consider splitting the column into separate columns. Use the Split Column feature in Power Query to segregate the numeric and text parts. After splitting, you can apply the correct data type to each resulting column—‘Number’ for the numeric column and ‘Text’ for the text column. This separation allows for accurate data typing and avoids forcing Power Query to convert text to numbers, which it cannot do.

In summary, resolving the “DataFormat.Error” involves: verifying intended data types, examining error details for specific clues, and strategically separating mixed data types into appropriate columns before applying type conversions. These steps will help you systematically troubleshoot and fix data conversion issues in Power Query.

Why is Excel Not Allowing Me to Convert to Number?¶

Excel, and by extension Power Query within Excel, may sometimes prevent you from directly converting values stored as text to numbers. Several reasons can contribute to this issue, often related to cell formatting or the presence of hidden characters.

One common reason is that the cells are explicitly formatted as ‘Text’. When a cell is formatted as text, Excel treats all input as literal text strings, even if they appear to be numbers. In this case, you need to change the cell formatting to ‘General’ or ‘Number’ before attempting to convert the content to a numeric data type. Select the cells, right-click, choose ‘Format Cells’, and then select the appropriate category under the ‘Number’ tab.

Another frequent cause is the presence of invisible characters within the cell content. These can include non-breaking spaces, special symbols, or control characters that are not visually apparent but are recognized by Excel as part of the text string. These characters prevent Excel from recognizing the cell content as a valid number. Using the CLEAN and TRIM functions in Power Query, as discussed earlier, can effectively remove these invisible characters. In Excel directly, you might use formulas like =TRIM(cell) and =CLEAN(cell) in a new column to clean the data, then copy and paste values back to the original column or use the cleaned data from the new column.

Furthermore, merged cells can sometimes restrict certain operations, including number conversion. If any of the affected cells are part of a merged cell range, Excel might impose limitations on data type conversions. Unmerging the cells before attempting to convert them to numbers can resolve this restriction. Select the merged cell, go to the ‘Home’ tab, and click on ‘Merge & Center’ to unmerge the cell.

In essence, if Excel is not allowing number conversion, check cell formatting, look for and remove invisible characters using CLEAN and TRIM, and ensure that the cells are not part of a merged range. Addressing these factors will typically enable you to successfully convert text representations of numbers into actual numeric data types in Excel.

---

If you found these solutions helpful in resolving your “DataFormat.Error” issues in Power Query, or if you have other methods you’ve used successfully, we encourage you to share your experiences and insights in the comments below! Your contributions can help others in the community tackle similar data conversion challenges.