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