PowerQuery null to date

tazeo

Board Regular
Joined
Feb 15, 2007
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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 :LOL:

Thanks in advance
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,362
Messages
6,171,640
Members
452,413
Latest member
N3edHelp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top