PowerQuery null to date

[tazeo]

So I have a column called Due Date, which comes across from excel as text, it will always have a couple of null entries.

I want to change these to a date preferably 14 days into the future.

Have read how I should be able to do this in the column, without creating a new one, but I can't even get it to change the null to a dummy date.

I have tried changing the column to a Date one, and the change null to a dummy date (1/1/2023) = Table.ReplaceValue(#"Changed Type",null,#date(2023, 1, 1),Replacer.ReplaceValue,{"Due Date"})
and then adjust the #date(2023, 1, 1) with something like DateTime.LocalNow() but it isn't working. If I get this right I believe I will be able to just add the days, but need to get the previous step sorted first.

I am sure I probably have headed down the wrong path, so rip it to shreds

Thanks in advance

 

[arthurbr]

To change the null to a dummy date select the column, right the header- select "replace with" then enter "null" ( no double quotes) in "value to find" and enter a date in "replace with" - ok
I don't know how to add days in the column itself, but adding a custom column and using the DateAddDays function will do the trick

 

[jdellasala]

If you use Date.AddDays, you'll need a date to add to. The function DateTime.LocalNow() will return the current date and time. If you want just today's date, wrap that in Date.From().

Start by right clicking the null value and replace it with any date. The code generated will use the #date( y, m, d) syntax as the replacement value. Replace that with Date.AddDays( Date.From( DateTime.LocalNow() ), 14 ).

That assumes the column is already in Date format.