How to Fix DataFormat.Error: We couldn’t convert to Number

In this respect, read the post to solve the 'DataFormat'. Unable to convert to number format. Working in Power Query within Excel or Power BI should ease data analysis and reporting. However, a lot of its users face the annoyance of data type conversion issues, especially when the formats are not consistent.

Problems with data interpretation by Power Query due to incompatible delimiters, different formats of data, or wrong regional settings will result in the following conversion error message:

Format for Data.We were unable to convert to the number format. [Error Details]:

This post will take you through the roots of the problem and effective remedies for such kinds of errors.

Properly format dataConversion to number failed

DataFormat.Error fixing requires Here are some of the few techniques we tried when dealing with data in Power Query, Excel, or Power BI but couldn't convert to numbers:

Remove the auto-populated "Change Type"

Remove non-numeric values

Clean and standardize your data before changing types.

Manually set locale and delimiters

Appropriately separate thousands and decimals

Let's go into detail regarding this.

1. Remove the "Change Type" step that happens by default

After data import, Power Query often appends a "Change Type" step to programmatically try to apply column data types based on the first 1,000 rows. Sometimes this may be problematic if your data contains types that clash with each other-for example, text and integer values in a column."Couldn't covert to Number".

To do this, on the Data tab in Excel, click the Get Data button. In Power BI, clicking on Transform Data will lead to the Launch Power Query Editor option.

Applied Steps pane, in Power Query Editor, is on the right-hand side of the window. If the 'Changed Type' step is already there, then Power Query has already auto assigned data types for you. To delete a step, click the X that will pop up next to it. Power Query will also stop automatically trying to convert data types as well.

After removing the 'Changed Type' step, verify the proper data type has been assigned to each column.

2. Remove non-numeric values

This may happen when text and numeric values are in the same column, such as "ABC 123," because Power Query would try to make all of those into a number. Here is a couple of things you can try to rectify this:

Change the data type for the column to 'Text', use Replace Values to correct the faulty value, and then revert back to 'Decimal Number'.

You can also achieve this using the Split Column feature based on the delimiter. Then, change the data type of one column to 'Text' and the other one to 'Number'.

You may clear entries that include invalid or non-numeric values before you can change the data type for that column to Number.

Once you filter out entries that are not numbers, you can safely change the data type for the original column to Number.

3. Clean and standardize data before type conversion.

Sometimes, when you copy data from the internet, Power Query is confused when it tries to convert or combine columns because there are non-breaking spaces, or symbols like '&' or ',' in the data.

To remove these, once the Transform menu is clicked, either the CLEAN or TRIM function can be selected. One removes extra spaces in the text, leaving only one space between words; the other removes all non-printable characters. This latter one is particularly useful when cleaning data brought into Power BI from elsewhere since it identifies hidden characters which might not be otherwise discovered.

Reset the data type of the column once these functions are applied.

4. Delimiters and locale can be set manually.

If regional settings exist in your data, it's very important to ensure that the correct delimiter is used along with the correct locale in Power Query. Suppose you work with European data where the comma is the decimal separator. You would have to change Power Query to the source locale manually at File>Options>Query Options>Regional Settings.

If it is a single column, you will be able to change the data type correctly using the appropriate regional settings by navigating to Change Type Using Locale.

When you import CSV files, you also have to manually change the delimiters to comma or semicolon depending on whether your data uses them.

5. correct separators for decimals and thousands

Finally, use the Replace Values to replace the faulty separator if the Power Query is not converting the numbers because the decimal points and thousand separators are mismatched. In this case, for European data, replace '.' with '.' to convert it to what is expected.

Indeed, knowing some of these most frequent roots of the "DataFormat.Error" will enable you to debug and solve this problem fast and easily in your applications.