Replace NULL with today's date

[Anonymous]

I have a table in Query Editor that is the result of a lookup for an end date scenario. What I'd like to do is replace the null values with today's date. 

I think I could create a calculated column on the front-end, but that would duplicate my data in a file that's already pretty large.

So to  be clear, I have this:

ID	Date
123	1/1/2019
234	(null)
345	1/2/2019
456	1/3/2019

And I'd like the end result to be this:

ID	Date
123	1/1/2019
234	4/8/2019
345	1/2/2019
456	1/3/2019

Where the 4/8/19 dynamically changes every day to match today's date.

[DonnaLouisa]

Hi wondering if someone can help on the topic above? I've tried the fix but think I'm putting DateTime.LocalNow() in the wrong place as I keep getting an errors in the collum rather than values.

Currently reads:

Table.ReplaceValue(#"Parsed Date","0000-00-00 00:00:00",DateTime.LocalNow(),Replacer.ReplaceValue,{"Column1.closure_date"})

Can anyone tell me what it should read to replace 0000-00-00 00:00:00 with today's date and time please?

Thanks!

[Anonymous · Accepted solution]

Figured this out.

1. In Query Editor, select the column and use Replace Values from the Transform ribbon to replace "null" with "1/1/2019" (quotes not used the dialogue). Hit OK.
2. In the Home ribbon, choose Query Editor and find the "Replaced Value" line just created. Power BI will have interpreted 1/1/2019 as #datetime(2019, 1, 1, 0, 0, 0). Replace that string with DateTime.LocalNow() and hit Done.

[a_m_wood]

I tried same but didn't work for me. Any suggestions?

[young351]

How can I get this to give me yesterday's instead of today?

Table.ReplaceValue(#"Replaced Value2",null,DateTime.LocalNow(),Replacer.ReplaceValue,{"End Time"})

[Anonymous]

Hi David

I followed your solution but did not see any cells with 2019, 1, 1, 0, 0, 0. Therefore couldn't replace the string with the dateTime.LocalNow() Is there something I did wrong/am missing?

Many thanks

James

[mussaenda]

ReplaceDate = IF (DateTable[Date] = blank(), today(), DateTable[Date])

this is in dax. If you don't want to add another column, you may take a look at forum

[carlosagarcia]

Worked like a charm.