Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

5 REPLIES 5
KristofferJ
New Member

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.

RR_25
Regular Visitor

RR_25_0-1642001710308.png

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!

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors