cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Griffin_BI
Frequent Visitor

DataFormat.Error: We couldn't convert to Number - but the column is number!

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?

 

Griffin_BI_0-1624026848011.png

 

 

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

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.

edhans_0-1624032610094.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

V-pazhen-msft
Community Support
Community Support

@Griffin_BI 

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.

View solution in original post

3 REPLIES 3
V-pazhen-msft
Community Support
Community Support

@Griffin_BI 

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.

View solution in original post

watkinnc
Super User
Super User

I would try selecting the column, then choose from the Format Dropdown in the GUI "Clean" which will remove all non-printable characters.

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
edhans
Super User
Super User

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.

edhans_0-1624032610094.png

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors