Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

21 REPLIES 21
sylvans
Frequent Visitor

Thanks to all previous contributors in this thread. Unfortunately, after 6 years, this "[DataFormat.Error] We couldn't convert to Number." error still haunts us. Strangely though, the fixes previously mentioned did not work for me.

 

The only way to resolve this error in my data table was to manually change (data) type within the default "Change Type" step (i.e. the 3rd step if you are importing from a single Excel file). Any other additional "Change Type" steps didn't work.

 

And one thing worth mentioning is the root cause of this error seems to be Power Query automatically determining each column's data type based on the top 999 rows. So, Power Query (Power BI) could be enhanced to deactivate certain columns from automatically detecting data type (like adding a setting icon next to the default "Change Type" step), it would probably help many users

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.

Thanks! This worked for me.

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?


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

It's in query editor

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors