Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Replace NULL with today's date

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 colu...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.
DonnaLouisa's avatar
DonnaLouisa
Regular Visitor
5 years ago

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!