Forum Discussion

freidus's avatar
freidus
Regular Visitor
8 years ago

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

Hi   I am a real beginner in Power BI and I would greatly appreciate some help.   I am importing a large number of rows from 8 csv files (about 3m rows).   A large number of these rows is comin...
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

Can you share the code part where the numbers are comverted, so we can see what Locale code you are using?

 

Does your input have decimal commas or decimal points?

Or maybe a mix of commas and points?

freidus's avatar
freidus
Regular Visitor
8 years ago

Thank you so much for your help.

 

The code is as follows:

 

= Table.TransformColumnTypes(#"Changed Type1", {{"Actual_Receipts", type number}}, "en-GB")

The numbers in this particular column are all negative (preceded with a - ) or are 0. Some of the numbers do not have decimal points (eg -145101) and some do (eg -2146457.58). It seems that the numbers with decimals are the ones resulting in errors.

 

As far as I can tell, all the numbers with decimals all have decimal points (none with decimal commas).

 

Thanks again so much!

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    8 years ago

    The only things I can think of:

    1. The decimal point is some other character than a normal period (which has decimal unicode 46), and/or:

    2. There are additional unprintable characters in your text. You can remove these by using Transform - Format - Clean.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    If you can upload an example of your data (copy paste from excel?) we can test some things for ourselves.

     

    You could try to replace the comma in your file with a freshly typed comma? I think the other guy is right, and that your commas arent actually commas :P