none
How to filter on rolling date? RRS feed

  • Question

  • is there a reference page somewhere for all the power query built in date functions? ie dateadd()?

    I'm reading data from an RSS feed (http://sentinel.ga.gov.au/RSS/sentinelrss.xml) but want to filter to recent observations (12rs) only.

    Adding a date filter using the gui adds a hardcoded #datetimezone(2014 etc etc) call

    I want to do something like

    = Table.SelectRows(ChangedType4, each [OBS Time] >  DateTimeZone.FixedUtcNow minus 12hours)

    is this possible? I see AddDays()/AddWeeks() methods but no add hours/minutes/seconds


    Jakub @ Adelaide, Australia

    Thursday, January 16, 2014 5:10 AM

Answers

  • There is no library function to do arithmetic over a DateTime value at the level of hours, but you can use the #duration directive to create a 12 hour duration and subtract it from the original datetime value.

    For example:

    Table.SelectRows(ChangedType4, each [OBS Time] >  #duration(0,12,0,0)

    You can read more about it in Power Query language specification:

    http://go.microsoft.com/fwlink/?LinkID=320633


    Zafar Abbas


    • Edited by Zafar Abbas Thursday, January 16, 2014 8:27 AM
    • Marked as answer by jakubk Thursday, January 16, 2014 11:45 PM
    Thursday, January 16, 2014 8:27 AM

All replies

  • There is no library function to do arithmetic over a DateTime value at the level of hours, but you can use the #duration directive to create a 12 hour duration and subtract it from the original datetime value.

    For example:

    Table.SelectRows(ChangedType4, each [OBS Time] >  #duration(0,12,0,0)

    You can read more about it in Power Query language specification:

    http://go.microsoft.com/fwlink/?LinkID=320633


    Zafar Abbas


    • Edited by Zafar Abbas Thursday, January 16, 2014 8:27 AM
    • Marked as answer by jakubk Thursday, January 16, 2014 11:45 PM
    Thursday, January 16, 2014 8:27 AM
  • Thanks zafar, using the link to that reference doc i was able to write the following working filter

    = Table.SelectRows(ChangedType4, each [OBS Time] > DateTimeZone.FixedUtcNow()- #duration(0,12,0,0) )


    Jakub @ Adelaide, Australia

    Thursday, January 16, 2014 11:45 PM