Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.