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 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:

 

IDDate
1231/1/2019
234(null)
3451/2/2019
456

1/3/2019

 

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

IDDate
1231/1/2019
2344/8/2019
3451/2/2019
4561/3/2019

 

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

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.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

    • young351's avatar
      young351
      Frequent Visitor
      4 years ago

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

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

    • a_m_wood's avatar
      a_m_wood
      Regular Visitor
      3 years ago

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

       

       

       

       

       

       

  • mussaenda's avatar
    mussaenda
    Community Champion
    7 years ago
    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

  • 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!