Forum Discussion
Replace Null Dates with Today's Date
Hello, I have a dataset which includes a date column where there are nulls. I'd like to replace these nulls with today's date.
So the example above should look something like this at the end:
[ActualCompDate]
7/13/2023
6/23/2023
7/13/2023
7/13/2023
7/13/2023
7/13/2023
7/13/2023
I have tried this method, but it literally just made all the dates "1/1/2019" - which is not what I was looking for.
So then I tried this method instead with M and am getting the following error:
Expression.Error: A cyclic reference was encountered during evaluation.
The formula I used with the second method:
Custom = Table.ReplaceValue(
#"Table",
each [ActualCompDate],
each if [ActualCompDate] = null then Date.From(DateTime.LocalNow) else [ActualCompDate],
Replacer.ReplaceValue,
{"ActualCompDate"}
)Will someone please help me with this? Thank you in advance!
Hi Anonymous
You can achieve it with 3 simple steps:
1. Add customed column :
DateTime.Date( DateTime.LocalNow() )2. Use it in condition and add Fixed column :
if [Date]=null then DateTime.Date( DateTime.LocalNow() )
else [Date]3. remove the original date column and change data type of added to date
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
4 Replies
Hi Anonymous
You can achieve it with 3 simple steps:
1. Add customed column :
DateTime.Date( DateTime.LocalNow() )2. Use it in condition and add Fixed column :
if [Date]=null then DateTime.Date( DateTime.LocalNow() )
else [Date]3. remove the original date column and change data type of added to date
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Happy to help ☺️
pls see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RiVSw1ipJO7nPhEFf?e=hNDrFe
Table.TransformColumns( #"Changed Type",{"Business Days", (x)=> if x= null then Date.From( DateTime.LocalNow()) else x})
