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
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.