DateTime Minus 7 Days

[BuiltInThe80s]

Hi All,
I have two parameters, both with a DateTime value:

Param1: 11/05/2019 12:00:00
Param2: 11/05/2019 19:00:00

I am passing these parameters into a query (which requires the date and time as text) using the following:

Text.From(Param1)
Text.From(Param2)

This works fine but I would also like to do another query where it takes the parameters and takes off 7 days. I have tried the following but it doesn't seem to be working:

Text.From(Date.AddDays(DateTime.Date(Param1)), -7) & " " & Text.From(DateTime.Time(Param1))
Text.From(Date.AddDays(DateTime.Date(Param2)), -7) & " " & Text.From(DateTime.Time(Param2))

Expected output should be:

10/29/2019 12:00:00
10/29/2019 19:00:00

Any help appreciated.

[Anonymous]

Hi @BuiltInThe80s,

Please modify your formula as below:

Text.From(Date.AddDays([Param1],-7))

Result would be show as below:

Hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BuiltInThe80s]

Thanks @Anonymous 

This does work for a custom column but unfortunatley doesn't work in my query. A little more info: I am concatenating the query as text in my query. For example, the following is working for the date/time parameter:

& Text.From(Param1) &

& Text.From(Param2) &

Using your advice it looks like:

& Text.From(Date.AddDays([Param1],-7)) &

& Text.From(Date.AddDays([Param2],-7)) &

This gives me the following error:

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

Removing the square brackets doesn't give an error but also doesn't return any results. Any ideas?