Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a column called data with type "any". Most of the entries in the column are integers, but there are some hex entries in the form like 0000082A. For some reason, even though it's not a number column, I am getting an error on the hex entries "DataFormat.Error: We couldn't convert to Number.". This error persists even if I change the column type to text.
This data comes from an azure storage table and has the rows expanded from the table as follows:
Before expanding:
error appears once expanded:
Auto "Changed Type" step:
Has anyone seen something like this before?
Thanks!
Solved! Go to Solution.
I found the solution was to open the advanced editor. There was an automatically added "Transform Sample File" created in my queries list. When I opened the advanced editor for this I could see that it had the data type changed to int64. When I switched that to "Type Any" it got rid of the error.
Thanks to @alex-nunes for the suggestion here:
https://community.powerbi.com/t5/Desktop/Error-quot-Can-t-convert-to-number-quot-but-the-collumn-typ...
I found the solution was to open the advanced editor. There was an automatically added "Transform Sample File" created in my queries list. When I opened the advanced editor for this I could see that it had the data type changed to int64. When I switched that to "Type Any" it got rid of the error.
Thanks to @alex-nunes for the suggestion here:
https://community.powerbi.com/t5/Desktop/Error-quot-Can-t-convert-to-number-quot-but-the-collumn-typ...
Hi @mussaenda,
The steps applied are shown in the pictures above. They are:
1. "Expanded Table Column1"
= Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
2. "Changed Type"
= Table.TransformColumnTypes(#"Expanded Table Column1",{{"recType", Int64.Type}})
These two steps were added automatically after expanding the columns, The error shows up immediately after these steps. In fact, the error is there after the first step listed.
Hi @mussaenda,
Like I said, the error shows up after the first step listed. If I remove the change type step, the error is still there.
I faced the same problem: the error message appears even after retyping the field as Text;
I was working on an excel source, and while importing the data, Power BI generates a step to set automatically a type to a column... after removing this step, the error disappeared!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.