Forum Discussion
DataFormat.Error: We couldn't convert to Number - but the column is number!
Because something in that column isn't a number. There is a non-numeric character in that column. Here I have created a table with the numbers 1-6, then a letter, then converted to a number.
It tells you at the bottom in the error code - Details: a - "a" was the character I typed in.
You need to either filter out that value first, replace it with something else using the Replace Values feature on the Transform ribbon, or fix the source data first.
Note you could have invisible data. If you paste data from a website, HTML will add all sorts of garbage to a cell in Excel that Power Query will have none of, but it is non-printing and often you cannot see it. You have to use CODE() in Excel to find it, or Character.ToNumber() in Power Query. ASCII char 160 is a good example of this.
If your cell contains text any string, it would be the error. Try split the text and number stings.
For example, if you have "ab 100" in a column cell, you can split it by blank and then turn the column with 100 into number type.
Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
I had this issue and the problem was that the imported numbers used . instead of ,
When I replaced all . with , using the replace tool it worked.
THIS is what resolved my issue, thank you!