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
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.
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.
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
I created a new topic here:
Still no solution for me though...
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")))
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.
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.
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
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.
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.
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 🙂
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?
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
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
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.
Is this in Query Editor or the data model?
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
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!