Replace NULL with blank value of format DateTime

[hhammeren]

I need to replace all null values in a column with a blank cell. The column is a DateTime format so when I type a blank in the "replace with" box it gives me the error that I need to enter a DateTime value.

Is there a blank DateTime value equivelant that I can us in the "replace with" box in order for teh cell to be blank or 0?

Thanks in advance!

Hannah

[Flinty]

I often have this problem when I'm working with the Power Query Editor when I want to perform sort of if statement calculation using the date column (formatted as dates) containing null values. I substitute null values for 1/1/1900 using the Replace Values function. Then I can use this as part of my calculation.

[v-huizhn-msft]

Hi @hhammeren,

Have you resolved your issue? Please mark the helpful and right reply as answer, so more people will benefit from here.

Best Regards,
Angelia

[v-huizhn-msft]

Hi @hhammeren,

You replace the null in Query Editor? If you don't replace, the null value will be blank when you add them into model. Please see my screenshot below.



For DateTime type value, you can create a calculated column transfer blank to 0, 0 display 1899/12/30, please view the screenshot below.

Column = IF(ISBLANK(Table1[DateTime]),0,Table1[DateTime])

Best Regards,
Angelia

[Amzad]

Its helpful to me @v-huizhn-msft     Thanks         

[Greg_Deckler]

First, I do not believe that DateTime values in the query editor can be "blank", they show up as "null". I guess my question is why? When you import them, they will be BLANK such that DAX ISBLANK will work and return true. What is the issue you are attempting to solve?

[Anonymous]

Hi, Greg. I am importing data from an excel that in itself is missing some dates(check screenshot),

Now, when i merge it with a date column that has the missing dates too so that i can use the values on the given date by filling up the null values. The orginal date column still has null values. I thought to change the nulls to "01/01/1000"(Check Screenshot) as cant use ISBLANK or BLANK.
But, Is there a function that i can use to check if  MAX[DATE]-1 = 01/01/1000  ,

Coz, max[DATE]-1  does not return the value in my column but the actual date -1.