Forum Discussion

tsuirs's avatar
tsuirs
Frequent Visitor
9 years ago
Solved

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 Er...
Need Help
  • tsuirs's avatar
    tsuirs
    9 years ago

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

     

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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.