none
Power Query/Power Pivot cannot convert negative numbers stored as text to numbers RRS feed

  • Question

  • I have a column of numbers, some positive, some negative, stored as text. When I change the data type for the column to number I get an error for all the negative numbers: DataFormat.Error: Could not convert to Number and the Details list the negative number. Looking for advice on how to change the data type in the query editor. I can't modify the database: it's from the CIA.


    • Edited by JustGini Tuesday, April 1, 2014 2:24 AM
    Tuesday, April 1, 2014 2:22 AM

Answers

  • Power Query can certainly convert the text "-1" into a negative number, so there's something else going on here. Is it possible, for instance, that the negative sign in the string is actually some kind of Unicode character (i.e. em-dash or en-dash) or that there's something which looks like a digit in the text but is actually a letter?

    You could try loading the data into Excel as text to help debug the problem.

    Tuesday, April 1, 2014 1:57 PM

All replies

  • Power Query can certainly convert the text "-1" into a negative number, so there's something else going on here. Is it possible, for instance, that the negative sign in the string is actually some kind of Unicode character (i.e. em-dash or en-dash) or that there's something which looks like a digit in the text but is actually a letter?

    You could try loading the data into Excel as text to help debug the problem.

    Tuesday, April 1, 2014 1:57 PM
  • I have a column of numbers, some positive, some negative, stored as text. When I change the data type for the column to number I get an error for all the negative numbers: DataFormat.Error: Could not convert to Number and the Details list the negative number. Looking for advice on how to change the data type in the query editor. I can't modify the database: it's from the CIA.


    I have the same problem here with Power Query. My report works on my computer but my colleague gets this error on his computer.
    • Edited by PLRD Monday, February 22, 2016 8:14 PM
    Monday, February 22, 2016 8:13 PM