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
SaDBuTruE
Regular 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
austinsense
Impactful Individual
Impactful Individual

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 🙂

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
Resident Rockstar
Resident Rockstar

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 

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.

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

Hi @SaDBuTruE,

 

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

 

Regards,
Xiaoxin Sheng

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

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.