none
Filter a dates column with a date passed as parameter RRS feed

  • Question

  • Hi, 

    I just rewrite and simplify the case to ease you helping me. I would appreciate your hints. I 'd like to filter the Date Column with a Date value passed as variable. My syntax rises an error. It works with a static value but not with variable. Static value works fine :

    let
        Source = Table1,
        #"Filtered Rows" = Table.SelectRows( Source , each ([DateText] = "01/02/2019")) 
     in
        #"Filtered Rows"

    ..but replacing the "01/02/2019" by the variable DateParamT :

    let
        Source = Table1,
        DateParamT=fnGetParameter("DateT"), // Load Date parameter as text : 01/02/2019
       #"Filtered Rows" = Table.SelectRows( Source , each ([DateText] = DateParamT))
    in
        #"Filtered Rows"


    This rises an error


    Thank you for any insight 






    • Edited by TristanGe Saturday, June 8, 2019 2:48 PM
    Sunday, June 2, 2019 6:11 PM

Answers

All replies

  • Hi,

    You should just replace first argument of Table.AddColumn function:

    #"AddedCustom1" = Table.AddColumn(#"Changed Type", "Filter", each if [Date] = DateParam2 then "Yes" else null)

    Monday, June 3, 2019 9:37 AM
  • Hi Aleksei, 

    thank you very much, ..., a stupid mistake.

    Nevertheless I came to the warning i often get : the damned Formula.Firewall 



    • Edited by TristanGe Saturday, June 8, 2019 8:32 AM
    Wednesday, June 5, 2019 9:10 PM
  • I know this instructive post, but I don't see it applicable in this case because the source query is already Staged out (Table1). And by splitting again this query "table2" I will loose the parameter value. I can't make it smaller. 

    I am not sure this error message relates to the real issue I am having. 

    Saturday, June 8, 2019 8:34 AM
  • Excel 365 Pro Plus with Power Query (aka Get & Transform)
    Fix the FireWall problem by clicking on
    "Ignore Privacy Levels" in PQ options.
    This is also expounded on by Gil Raviv in his book
    "Collect, Combine, and Transform Data Using Power Query..." on pg 190.
    Attached example exhibits this oh-so-great FireWall feature.
    http://www.mediafire.com/file/mjrm61zp4yhn28t/06_09_19.xlsx/file
    http://www.mediafire.com/file/n5y9mr3ntrs2xkq/06_09_19.pdf/file

    Sunday, June 9, 2019 4:12 PM
  • Hi Tristan. Try moving the DateParamT step into its own query.

    Ehren

    Friday, June 14, 2019 7:09 PM
    Owner
  • Hi Herbert, 

    !!! thank you so much. You fixed it, it works immediately. I was convinced it wasn't my coding but I didn't know at all how to fix it. Luckily I found a weird workaround.

    On my new testing code below : If you stop on step "Added Custom" you get firewall, if you continue to then next step "Source" it completes correctly.

    But if you withdraw Step "Added Custom" then "Source" failed :-) .

    So I kept the unnecessary "Added Custom" to make "Source" work properly.

    Now with you insight : Option  "Ignore privacy level ..." , "Added Custom" works and can be deleted. 

    Thank you

    FDate=Date.From(fnGetParameter("Date")), 

    TFDate=Text.From(FDate),
        MyTable = Table1,
        #"Changed Type" = Table.TransformColumnTypes(MyTable,{{"Date", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Date]=TFDate then "yes" else "no"),
        Source = Table.SelectRows(#"Added Custom", each [Date] = TFDate )
    in
        Source

    Monday, June 17, 2019 5:21 AM