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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lachlanP
Helper II
Helper II

"We couldnt convert to a number" error on a column with type "Any"

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:

lachlanP_0-1660339506827.png

error appears once expanded:

lachlanP_1-1660339596347.png

Auto "Changed Type" step:

lachlanP_2-1660339669249.png

lachlanP_3-1660339683243.png

 

Has anyone seen something like this before?

 

Thanks!

 

1 ACCEPTED SOLUTION
lachlanP
Helper II
Helper II

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...

View solution in original post

5 REPLIES 5
lachlanP
Helper II
Helper II

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...

mussaenda
Super User
Super User

Hi @lachlanP ,

 

What are your applied steps?

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

 

try to remove the Change Type step

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors