Forum Discussion

BuiltInThe80s's avatar
BuiltInThe80s
New Member
6 years ago

DateTime Minus 7 Days

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.
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      BuiltInThe80s
      New Member
      6 years ago

      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?