Forum Discussion
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...
Figured this out.
- 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.
- 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.
Figured this out.
- 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.
- 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.
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
How can I get this to give me yesterday's instead of today?
Table.ReplaceValue(#"Replaced Value2",null,DateTime.LocalNow(),Replacer.ReplaceValue,{"End Time"})
I tried same but didn't work for me. Any suggestions?
