DataFormat.Error: We couldn't convert to Number.

[Johannf]

When trying to convert a column containing only numbers, I get an error "DataFormat.Error: We couldn't convert to Number."

I have searched and replaced the column for spaces and replaced it.

I have searched and replaced the column for all special characters, as listed in the advanced search and replace dialog.

Our decimal separator is set to a full stop.

When right clicking clicking on Decimal Number, I get the error.

Here is an example of the error and that data.

Please help.

[Johannf · Accepted solution]

If I convert the datatype from text to decimal by clicking on the data type block, than clicking on "1.2 Decimal Number" I get the error.

However, if I click on the last option "Using Locale...", then select on "Decimal number" and then select  "English (United States)" It works. If I select any other locale, it does not work. This seems to be a but in Power query.

[mlsx4]

Hi @Johannf 

As I commented previously I think the problem is the dot. For some reason, Power BI is considering it as miles, then when you try to convert if the value is 226.39, Power BI is trying to put  226390 so it gives an error due to the missing 0.

If you replace it by comma you should be able to convert to decimal value too.

[Johannf]

Yes, it can convert it to decimal when I replace the dot with a comma. That is how I used to do it, but that is not the best way. Our decimal separator is a dot. The conversion using locale is a better solution.

[mussaenda]

Hi @Johannf ,

what does your data look like before changing the type to decimal?

[mlsx4]

Hi @Johannf 

Could you try to change "." by ","? I think it is the problem