none
Using multi date parameters in Power Query RRS feed

  • Question

  • Hi

    I want to select rows within a date range. I have tried without parameters which works:

    let
    
     
      Source =
    OData.Feed("http://t480-wa-ref.ev.dk.local:7048/BC130/OData/", null,
    [Implementation="2.0"]),
    
     
      FinansPoster_table =
    Source{[Name="FinansPoster",Signature="table"]}[Data],
    
     
      #"Filtered Rows" = Table.SelectRows(FinansPoster_table, each
    [Posting_Date] > #datetime(2020, 1, 1, 0, 0, 0) and [Posting_Date] <
    #datetime(2020, 1, 31, 0, 0, 0))
    
    in
    
     
      #"Filtered Rows"

     

    But trying to do the same with parameters fails:

     

    let
    
    Source = OData.Feed("http://t480-wa-ref.ev.dk.local:7048/BC130/OData/", null,
    [Implementation="2.0"]),
    
     
    FinansPoster_table = Source{[Name="FinansPoster",Signature="table"]}[Data],
    
     
    #"Filtered Rows" =
    #!"Table.SelectRows(FinansPoster_table, each [Posting_Date] >=
    StartDate AND each [Posting_Date] <= EndDate)"
    
    in
    
    #"Filtered Rows"

     

    With the following error: Expression.SyntaxError: Token Comma expected. pointing at the AND in the filter statement.

    What am I missing?

    /René


    Monday, June 1, 2020 8:12 AM

Answers

  • Hi René,

    At first sight, you should use AND in lowercase and remove the second each keyword.

    Monday, June 1, 2020 8:26 AM
  • Your question did lead to "my" error. StartDate and EndDate comes from Excel where I loaded them via the From/Table Range function. I did forget to use the Drill Down function to make it usable for the Query function.

    It must have happened via my many attemps to find a way. And by this fix your first suggestion do work.

    You are my herro for today :-)

    /René

     
    • Marked as answer by Fuhlendorf Monday, June 1, 2020 10:08 AM
    Monday, June 1, 2020 10:07 AM

All replies

  • Hi René,

    At first sight, you should use AND in lowercase and remove the second each keyword.

    Monday, June 1, 2020 8:26 AM
  • Thanks for your reply :-)

    I did try the following then: 

    = Table.SelectRows(FinansPoster_table, each [Posting_Date] >= StartDate and [Posting_Date] <= EndDate)

    But it returns this error:

    Expression.Error: We cannot apply operator < to types Table and DateTime.
    Details:
        Operator=<
        Left=Table
        Right=01-12-2020 00:00:00

    :-(

    /René

    Monday, June 1, 2020 9:29 AM
  • let Source = OData.Feed("http://t480-wa-ref.ev.dk.local:7048/BC130/OData/", null, [Implementation="2.0"]), FinansPoster_table = Source{[Name="FinansPoster",Signature="table"]}[Data],

    StartDate = #date(2020,01,01), //remove this line and replace it by the next line with your date reference
    //StartDate = your StartDate reference,
    EndDate = #date(2020,05,30), //remove this lime and replace by it the next line with your date reference
    //EndDate = your EndDate reference,
    Filter =Table.SelectRows(
    FinansPoster_table,
    each
    [Posting_Date]>=StartDate
    and [Posting_Date]<=EndDate) in Filter


    Where are your StartDate and EndDate coming from ?




    • Edited by anthony34 Monday, June 1, 2020 9:46 AM
    Monday, June 1, 2020 9:41 AM
  • Your question did lead to "my" error. StartDate and EndDate comes from Excel where I loaded them via the From/Table Range function. I did forget to use the Drill Down function to make it usable for the Query function.

    It must have happened via my many attemps to find a way. And by this fix your first suggestion do work.

    You are my herro for today :-)

    /René

     
    • Marked as answer by Fuhlendorf Monday, June 1, 2020 10:08 AM
    Monday, June 1, 2020 10:07 AM