cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors