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

[Cartans1925]

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

[sylvans]

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

[sylvans]

An update to this error and my workarounds for it:

Index	Workaround description	Shortfall	References or Resources
1	Undertake an 1-off Data Type Validation in Excel before any PQY or PBI dev.	A one-off manual effort is required. This Excel wst is then copied, cleansed and transformed as Nested List obj inside PQY. Next, this obj shall replace the list within the default post-import "Changed Type" step. But the benefits include aligning every data type (aka every cols) for layman/end-users without any chance of error in the long run. Alternatively, end-users can customize data types.	Resolution found: Define Dynamic Data Types in Power Query by Goodly (https://www.youtube.com/watch?v=lD-7Tt8-oOk) Also: The Magic of Working with Lists in Power Query by Goodly (https://www.youtube.com/watch?v=90atXaUhBec)
2	Amending the data type downstream in Power BI frontend.	User-friendlier but the data values with metadata errors probably got stuck and lost during power query processing Also, users might have to perform this again & again when importing new data or refreshing	Using the data profiling tools - Power Query | Microsoft Learn Data types in Power Query - Power Query | Microsoft Learn
3	Alternative Resolution: Schema View within Power Query Online	NOT feasible because it’s not currently supported in our Microsoft 365 version/licence	Using Schema view (Preview) - Power Query | Microsoft Learn

[alex-nunes]

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

[lachlanP]

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.

[Keith011]

@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]

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

[lachlanP]

I created a new topic here:

https://community.powerbi.com/t5/Power-Query/quot-We-couldnt-convert-to-a-number-quot-error-on-a-col...

Still no solution for me though...

[lachlanP]

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.

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

[CesarSig]

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.

[Brendan_AW]

Thanks! This worked for me.

[v-xjiin-msft]

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.

[Cartans1925]

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

[v-xjiin-msft]

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]

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 🙂 

[Cartans1925]

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

[v-xjiin-msft]

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.

[Cartans1925]

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"

[Cartans1925]

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

[v-xjiin-msft]

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]

Is this in Query Editor or the data model?