Error Convert Text to Number

[Anonymous]

Hi, 

I tried to convert one column, which has been automatically exported as text, to decimal number, but I got below error. Do you know how I can fix this? 

 

[amitchandak · Accepted solution]

@ehsanbh , check for space.

Create a new column with Text.Trim and convert it to decimal and see if it works. It means it's a problem to have white space.

if necessary, see how to handle the error in the power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

[quyenduong]

I read the solution but still cannot solve this problem. My number is like this 123,456 and when I change data type in power query, the whole columns show errors. What do I have to do? Thank you

[Syndicate_Admin]

Replace the "," with nothing and redefine the variable type

[quyenduong]

Thank you so much. It works now but just for one column, I have a problem that all of my numerical data always automatically has comma between number. Do you know any setting to get rid of this in the future?

[Syndicate_Admin]

I had the same mistake and solved it like this:

1. Enter the query edit

2. Right click on a column cell and Replace values " " for nothing

3. Under Data Types change text by number

I had the same problem and it is my solution:

1. Enter to the query

2. Right clic on a cell in the column, replace values " " for nothing

3. Change tipe of variable for number

Its work

[amitchandak · Accepted solution]

@ehsanbh , check for space.

Create a new column with Text.Trim and convert it to decimal and see if it works. It means it's a problem to have white space.

if necessary, see how to handle the error in the power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

[AllisonKennedy]

I'm guessing there's either a space after the number or a hidden formatting like html code that is preventing it from being a pure number. Try doing a clean and trim, then change data type to number.

What's the data source?

[Anonymous]

@AllisonKennedy Trim worked perfectly! Thanks a lot. These are electricity data I am reading from a CSV file.

[AllisonKennedy · Accepted solution]

@ehsanbh You are welcome. I'm glad it worked.

PS, you can mark more than one answer as a solution, since Amit and I responded around the same idea with similar suggestions - great minds think the same. 🙂