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
Anonymous
Not applicable

Error Convert Text to Number

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? 

1.JPG2.JPG3.JPG 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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

View solution in original post

@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. 🙂

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

8 REPLIES 8
quyenduong
Helper I
Helper I

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

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

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
Administrator
Administrator

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
Super User
Super User

@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
Super User
Super User

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?

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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

@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. 🙂

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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.