cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cartans1925
Regular Visitor

Error: "Can't convert to number", but the collumn type is text

I have a collumn that was previously type number (and the data in it was all numbers, obviously). Now I changed the content to text, aswell as the collumn type, but the error message shows

19 REPLIES 19
alex-nunes
Regular Visitor

I have experienced the same issue as you, and found the following solution by editing the query using the Advanced Editor. I edited the type of my 'wrongly' detected data columns. See attached image. "Assigned ID" was detected as "Int64.Type" and "Transit" as "any". I changed both with "type text" and the issue was resolved.

 

Reason (I guess, as I am still not familiar with Power Query/BI): On the first iteration of query, my data was detected as Int64.Type. However, after I entered some text information in the original excel columns, the query always showed "can't convert to number" error message, even if I added another step of changing the column type (at query) to text.

 

Hope this helps.

Alex

Advanced editor query.png

Thanks @alex-nunes , This solved my problem too.

For me, when I expanded a content field there were some additional "helper queries" automatically created. When I looked at the advanced editor for these I could see that it had the type mistakenly set to int64. I changed it to Any and it solved my problem.

@alex-nunes 

Great find. I did solve my issue as well. It was due to the auto-detect data type option that was enabled in the settings of Power BI Dekstop.

 

What I did was turning it off so there won't be an auto-applied step of "Changed Type". This will solve the problem

Keith011
Helper III
Helper III

any solution to this ? or whats the problem? Im facing the same issue here

lachlanP
Helper II
Helper II

Did anyone ever find a solution to this? I'm experiencing a very similar issue. I have a data source where I expanded a table column and have a new column called 'data'.  Immediately after expanding, the type is auto-set to "any", but I get a DataFormat error "couldnt convert to a number". this error persistes if I change the column to a text column.

lachlanP_0-1660334138664.png

= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

lachlanP_1-1660334171184.png

lachlanP_2-1660334187301.png

 

CesarSig
Regular Visitor

There should be a "Changed Type" step that power query creates automatically, if you select that step and change the data type for the column that has errors it should fix it. When you apply a step afterwards it taking into account what power query applied initially.

v-xjiin-msft
Solution Sage
Solution Sage

Hi @Cartans1925,

 

According to your description, you have a number type column and now you want to convert it to text type column. However, it returns an error as "Can't convert to number". Right?

 

If I misunderstood your description, please feel free to correct me. Generally, if you convert number to text, the error should be like "Can't convert to text" not "number".  How did you do the converting operation? Could you please share us your pbix file if possible? It can help us understand your issus more clearly and get a right direction.

 

Thanks,
Xi Jin.

No, I had a collumn, all of it was numbers, (1,2,3) and it had the datatype "number". I changed this collumn to be all text like (January, february, march) aswell as the collumntype (it is now text). But the error message shows "Can't convert to number" in despite of the datatype of the collumn being text.

I'm sorry, but I can't share it, it is protected by NDAs etc

Hi @Cartans1925,

 

So, in your source file there's a "number" column which stores numbers. You have changed this column to text with values as (January, february, march). Then you refresh the dataset in Power BI and the error shows up. Right?

 

You should know that, once you load your data into Power BI. In Query Edit, all the information of your dataset will be recorded including the column type. You can verify this in Changed Type at Applied Steps pane. And these information will not be modified automatically.

 

It also means, for the first time you load your data into Power BI, this "number" column was recorded as a number type column. Then you changed the column to text and refreshed the dataset. However for this column in Power BI, it was still a number type column and could not store text values. That's why the error shows up.

 

To resolve your issue, you have to convert the column type manually in Query Edit.

 

Thanks,
Xi Jin.

Anonymous
Not applicable

Hello! I am having the same issue with my data. It was originally uploaded as '1' and '0', but then I converted the raw data to turn these to 'yes' and 'no'

 

I guess PowerBI has now set the column to recognise numbers and queries the text in the box; "DataFormat.Error: We couldn't convert to Number.
Details:
Yes"

 

I'm in the Data Query Editor, have chaned the column type to 'text' but the error message still remains, how do I get it to change the data to yes and no?

 

Thanks very much if anyone can help 🙂 

That's my problem, @v-xjiin-msft, I did convert it to text and the error still appears.

Hi @Cartans1925,

 

Could you please share us some sample data which can help us repro your issue or share us your pbix file with OneDrive or something else if possible?

 

Thanks,
Xi Jin.

A print of the data in excel, the collumntype in powerbi+error cells and finally, the error message. It is written in portuguese, translates as "Data.Format.Error. We couldn't convert to number
                                     Details:
                                          Abril"

 

prints power bi.png

I actually """""solved""""" this by making an identical collumn and it recognized it as text, but nevertheless it's important to understand what went wrong there

Hi @Cartans1925,

 

I'm glad to hear that you have resolved your issue.

 

But we can still continue to troubleshoot the error. As you said that you have already converted it to text. Could you please share us your steps about converting? So that I can try to repro your issue.

 

Thanks,
Xi Jin.

Greg_Deckler
Super User
Super User

Is this in Query Editor or the data model?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

It's in query editor

Cartans1925
Regular Visitor

I had a collumn with all numbers and number data type. Now I changed the collumn to be all text aswell as text data type. But there's an error message saying the text can't be converted to number, in despite of my change in the data type

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.