Convert text to number

[SaDBuTruE]

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 ?

[austinsense]

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.

[SaDBuTruE]

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.

[Baskar]

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 

[SaDBuTruE]

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.

[v-shex-msft]

Hi @SaDBuTruE,

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

Regards,

Xiaoxin Sheng

[SaDBuTruE]

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.

[v-shex-msft]

Hi @SaDBuTruE,

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

Regards,
Xiaoxin Sheng