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

Convert text to number

Hello,

 

Soon after unpivoted other coloms I received a message " We couldn't convert to numbers". None of cells include +%&* type of characters and all figures in the cell is in text format.

 

Can you help to fix this issue ?

 

Capture.JPG

7 REPLIES 7
Super User
Super User

Re: Convert text to number

Can u please tell me the unpivoted columns data types .

 

It is because of mixed data type. 

 

u can do one thing chage it as text type and check or share some sample data 

austinsense Established Member
Established Member

Re: Convert text to number

This is one of my least favorite errors in Power Query :/

 

First delete the unpivot step. Try converting the columns to numbers. The best way to figure out the issue is to then "Replace Errors" on all the columns with a number like 999,999,999 then run the query and see which column/row is causing the problem.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast Smiley Happy
SaDBuTruE Frequent Visitor
Frequent Visitor

Re: Convert text to number

One more thing that I should have told you, supposing there are 10 coloms including 3 of decimal numbers. The colomn shows "We couldn't conver to numbers" message should be in text format. Because it is a channel name for the sales (i.e. 1.BAYI=1.STORE 2.STORE). What I do not understand is these coloms expect for the sales,cost of sales and amount are in text types and I'm not trying to convert to numbers. Why do I received such issue ?

 

Therefore thank you for your response, but I am not trying to convert that colomn to numbers so your response is not plausible for me.

SaDBuTruE Frequent Visitor
Frequent Visitor

Re: Convert text to number

Before text columns are unpivotted, all coloms are changed to text type. After that, only amounts such as sales,cost of sales and quantity are changed to decimal numbers format. Thus, in the column one there are no mix types of data.

Community Support Team
Community Support Team

Re: Convert text to number

Hi @SaDBuTruE,

 

Can you please share a sample file? I test with sample data and can't reproduce your issue.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
SaDBuTruE Frequent Visitor
Frequent Visitor

Re: Convert text to number

Hi @v-shex-msft

 

Do you know any website that can be freely uploaded vast data ? or Give me your e-mail address so that I can sent data to you.

Community Support Team
Community Support Team

Re: Convert text to number

Hi @SaDBuTruE,

 

You can upload it to 1drive and share a link here.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |