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

[tsuirs]

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?

[Anonymous]

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

[Anonymous]

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! 

[MarcelBeug]

You're welcome.

[tsuirs · Accepted solution]

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

[v-haibl-msft]

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

Best Regards,
Herbert

[Phil_Seamark]

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.

[MarcelBeug]

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.