Null values issue in Power Query

[SbusyCR]

Hi Champions!

Please refer to the attachments. I am not able to upload the two source files.

Once I change the Data Type to text in forum.xlsx in the Word # from the beginning of the Quran.2 column I am able to replace "null" for no text. However, because I need this column as Whole Number Data Type, when I go back to Whole Number, the "null" shows again.

How can I solve this?

 

[Chihiro]

It's bit difficult to help you without sample. I'd recommend uploading small file that is representative of your source file(s). 20 or so rows each should be enough.

In general, if it's "null" in italics and in small case, it's true null and you shouldn't have to worry about it. Is there any particular reason you don't want the null value?

 

[SbusyCR]

Thanks for stepping in Chihiro! Smaller files attached.

 

[Chihiro]

So what should "null" be? Should it be 0? Or blank?

If it's blank. Just leave it as is. When you load to sheet, or evaluate it in Data model. It will be treated as blank.