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...
freidus's avatar
freidus
Regular Visitor
8 years ago

Thank you both for your suggestions. However, I have tried what you have suggested with no luck.

 

Even when I change the type to text, which I understand should be able to read anything, it doesn't work with these specific cells.

 

The delimiter that I am using is commas. When I view the file in Excel, there is no issue with these numbers and they are dealt with correctly.

 

I should note that in the columns where I am having the issue, it is only the numbers with decimal points that are not being read correctly.

  • 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.