Adding days to a date using M...

[Matty]

Hi Team,

Is there a way to update an existing date column in Power Query by means of adding or subtracting days?

I can use the function Date.AddDays to create a new column to do this, but it'd be neater to change the existing column rather than having to create a new one.

I've had a look around for ways of doing this, but I can't find anything obvious.

Cheers,

Matty

 

[macfuller]

You can subtract days using the filter function, but as far as adding dates... the source data is the data. PowerQuery doesn't add data (rows) to columns it pulls, only to those it creates.

 

[Matty]

You can subtract days using the filter function, but as far as adding dates... the source data is the data. PowerQuery doesn't add data (rows) to columns it pulls, only to those it creates.

Thanks. I just wanted to make sure I wasn't adding steps to the script that weren't needed.

Cheers,

Matty

 

[MarcelBeug]

This looks like a misunderstanding.

You can select your date column, choose one of the transform options for date (e.g. start of month) and then adjust the generated code to something like:

= Table.TransformColumns(Source,{{"Date", [B]each Date.AddDays(_,3)[/B], type date}})

 

[Matty]

Thanks Marcel - that neatens up my script and avoids a lot of unnecessary steps!

Cheers,

Matty

 

[Sphinx404]

This looks like a misunderstanding.

You can select your date column, choose one of the transform options for date (e.g. start of month) and then adjust the generated code to something like:

= Table.TransformColumns(Source,{{"Date", [B]each Date.AddDays(_,3)[/B], type date}})

You have several of these questions answers throughout the internet... however, you only specify how to ADD to the date... I am hoping to figure out how to subtract one day from the date.

I am trying to write a TRUE/FALSE that lets me know if the date was yesterday.

Closest I can think of

I'm an idiot...

Date.IsInPreviousDay

UGH!

=Date.IsInCurrentDay[Date] - 1 but I don't know how to write the "-1" to M can understand what I'm trying to do.

 

[sandy666]

maybe:

+ #date(0,0,-1)

 

[Behelith]

You can use the code from Sphinx404 to subtract simply by setting a negative number.

= Table.TransformColumns(Source,{{"Date", [B]each Date.AddDays(_,[COLOR=#ff0000]-3[/COLOR])[/B], type date}})