Search code examples
excelpowerquerym

How to return values between two timestamps in powerquery?

I am trying to return values in a query between two UTC timestamps in Powerquery in Excel, but I cannot get the syntax correct, please can you advise?

I have had success by using the following code to return anything from a certain date, but this also includes any subsequent dates: 

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", 
    each [TimestampUTC] >= #datetime(pReadYear, pReadMonth, pReadDay, 0, 0, 0)

so I modified it to the following....

#"Filtered Rows" = Table.SelectRows(#"Removed Columns", 
    each if [TimestampUTC] >= #datetime(pReadYear, pReadMonth, pReadDay, 0, 0, 0) 
        or [TimestampUTC] <= #datetime(pReadYear, pReadMonth, pReadDay+1, 0, 0, 0))

...but it keeps giving me a "Token Then Expected" error. All help greatly appreciated.

Solution

  • When you use if, it expects it to be of the form:

    if <condition> then <condition is true result> else <condition is false result>

    I'm guessing what you want is a simple and (not an or) without the if.

    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", 
    each [TimestampUTC] >= #datetime(pReadYear, pReadMonth, pReadDay, 0, 0, 0) 
        and [TimestampUTC] <= #datetime(pReadYear, pReadMonth, pReadDay+1, 0, 0, 0))