Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am connecting to an excel workbook on sharepoint using the web connector. I have a column of number values e.g. 1 of the "ABC 123" type when they come in. When I try to change this to "123" number I get an error message saying DataFormat.Error: We couldn't convert to Number. I don't understand how this could fail. Any ideas please?
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIf 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.
Hi Community,
I dont know if this is already asked here, i just cant fix the errors. I tried removed errors but it still there.
"DataFormat.Error: We couldn't convert to Number. Details: not in SMART"
Please help on this. thank you!
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 would try selecting the column, then choose from the Format Dropdown in the GUI "Clean" which will remove all non-printable characters.
--Nate
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting