cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Error: Cannot convert to number for Number column in Excel with null values

I imported an Excel workbook with data detection set to based on first 200 rows.

For one column in Excel, it contains whole number and there are a few rows  with null value.

 

Power BI returned Error - cannot convert to number for those rows with null value.

If I do replace errors and type null, it will resolve the issue.

Note if I select data detection based on all rows, it will set column type to Text instead of Whole numbers which is not what I want.

 

Question is how come Power BI during import will not recognize it is a null value and set accordingly?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Found the issue.  There is unprintable characters in the cell.  Once fixed, everything is fine.  Thanks for everyone help!

 

 

View solution in original post

8 REPLIES 8
Highlighted
Microsoft
Microsoft

Hi @tsuirs

 

You can use some features in the Query Editor to "clean" the column up before importing to Power BI, such as replacing null values with blanks, or 0's (or filtering out all together).

 

You can also use the remove rows feature in the query editor as another option.

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Highlighted

If I import an Excel sheet with whole numbers and blank cells (either empty or ""), the detected type is Int64.Type without any errors and null in the blank cells. To my surrprise, even cells with a space or the text "null" are loaded as null in Power Query and these are empty in the data model.

 

Only if there is some other text in the cells, I get errors when loading the data with Int64.Type.

 

Maybe you can double check if there is no text in the cells that are loaded in error.

Also check for any unprintable characters using LEN in Excel or Text.Length in Power Query.

 

 

 

 

 

Specializing in Power Query Formula Language (M)
Highlighted
Microsoft
Microsoft

@tsuirs

 

Could you please provide the excel workbook which can be used to repro this issue? I tested on my side but cannot repro the same issue. Please refer to following screenshot.

 

Cannot convert to number for Number column in Excel with null values_1.jpg

 

Best Regards,
Herbert

Highlighted
Impactful Individual
Impactful Individual

 
Highlighted
Frequent Visitor

Found the issue.  There is unprintable characters in the cell.  Once fixed, everything is fine.  Thanks for everyone help!

 

 

View solution in original post

Highlighted
Community Champion
Community Champion

You're welcome.

Specializing in Power Query Formula Language (M)
Highlighted
Regular Visitor

I am facing the same issue but in only 5 specific cells. When I reove the values, the error goes away. If I again put any values in those 5 cells, the error pops up again. I have wasted so much time trying to figure it out but nothing seems to work. I even created a completely new file and only pasted values in it but same error comes. 

 

The excel file is available here. Need your help guys!!! 

https://docs.google.com/file/d/1nxfpRn5SMGtE8_Z0S82mKE9OzGLVjb5b/edit?filetype=msexcel

 

fahadmunir7_0-1600609542469.png

 

Highlighted

fahadmunir7_0-1601192895158.png

The error I get is cannot convert to date. The data being used is numbers and the format is also in decimal but I do not know why power bi is trying to convert it to date. Please help! 

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors