Blank cells importing as Null

[Lapwing]

Hi there

I am importing .xls files exported from a Clarity database using Power Query and Power Pivot. Some numeric columns have blank cells which import as Null. I cannot change the data type to Whole Number or Decimal Number unless I search and replace the Null to a zero. Is there a way to get around this. I how do I get a blank cell rather than a Null? The search and replace will not let me replace Null with nothing.

 

[Lapwing]

I should add that I am using Office 365 Excel 2013.

 

[olivierhbh]

Hello Lapwing,

You should be able to set the column as numeric even with null values in it, no need to change them to 0 or anything. Could you double check or post an example workbook?
Null will be considered as blank in Power Pivot.

Are you sure you don't have text values in the column? Or maybe spaces.

Olivier.

 

[Lapwing]

Thanks Olivier. This is so weird. I have been struggling with this for the last few days after reading your reply it suddenly started working. No change to anything. I seen this sort of thing before and am convinced that computers are still in the realms of voodoo.

 

[olivierhbh]

No problem, glad you solved it!

Olivier.