none
Pass Date To Where Clause In Power Query RRS feed

  • Question

  • When I attempt the above I get an error of d1 does not exist.  I defined d1 as a parameter in my table parameters that I created, what is throwing the error that it does not exist, and what do I need to alter so that this functions as I want.  In a table called parameters I have the following

    Parameter --- Value

    d1                  04/01/2016

    d2                  04/22/2016

    And my actual power query syntax is

    let
        d1 = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
        d1_Value = d1 {0}[Value],
        d2 = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
        d2_Value = d2 {1}[Value],
        Source = Odbc.Query("dsn=mysql", "Select * from employees where birthday between d1_Value and d2_Value ORDER BY empid ASC")
    in
        Source


    I also tried to run my query like this with the where clause

     BETWEEN #" & d1 & "# AND #" & d2 & "# 

    Which threw a whole new error of Expression.Error: We cannot apply operator & to types Text and DateTime.

    Thursday, April 28, 2016 2:09 PM

Answers

  • Try this:

    let
        Parameter  = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
        d1 = Text.From(Parameter{0}[Value]),
        d2 = Text.From(Parameter{1}[Value]),
        Source = Odbc.Query("dsn=mysql", "Select * from employees where BETWEEN '" & d1 & "' AND '" & d2 & "' ORDER BY empid ASC")
    in
        Source

    Ehren

    • Marked as answer by IndigoMontoya Friday, April 29, 2016 1:38 PM
    Thursday, April 28, 2016 11:39 PM
    Owner

All replies

  • I have this working:

    let
        d1 = Excel.CurrentWorkbook(){[Name="Params"]}[Content],
        d1_Value = d1{0}[Value],
        d2_Value = d1{1}[Value]
    in
        d2_Value
    Params is a table name in my model with two columns "Parameter" and "Value". Your single call to get the table (d1) actually returns all rows of the table, so you do not have to call it twice. Make sure your table is actually a real table and that you have gone to the Table Design in the ribbon and named it the right name.


    Thursday, April 28, 2016 2:28 PM
  • I have this working:

    let
        d1 = Excel.CurrentWorkbook(){[Name="Params"]}[Content],
        d1_Value = d1{0}[Value],
        d2_Value = d1{1}[Value]
    in
        d2_Value
    Params is a table name in my model with two columns "Parameter" and "Value". Your single call to get the table (d1) actually returns all rows of the table, so you do not have to call it twice.

    Using your above syntax and formatting my where clause to use d1_Value & d2_Value threw an error of:

    We cannot apply operator & to types Text and DateTime.

    Thursday, April 28, 2016 2:30 PM
  • I now have this syntax which is giving me the error of "Token Comma Expected"

    let
        Parameter  = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
        d1 = Table.TransformColumnTypes(Parameter ,{{"d1", type text}}),
        d2 = Table.TransformColumnTypes(Parameter ,{{"d2", type text}}),
        Source = Odbc.Query("dsn=mysql", ""Select * from employees where BETWEEN '"&#"d1" & "'" AND '"&#"d2" & "'"  ORDER BY empid ASC")
    in
        Source

    Thursday, April 28, 2016 2:38 PM
  • Try this:

    let
        Parameter  = Excel.CurrentWorkbook(){[Name="Parameter"]}[Content],
        d1 = Text.From(Parameter{0}[Value]),
        d2 = Text.From(Parameter{1}[Value]),
        Source = Odbc.Query("dsn=mysql", "Select * from employees where BETWEEN '" & d1 & "' AND '" & d2 & "' ORDER BY empid ASC")
    in
        Source

    Ehren

    • Marked as answer by IndigoMontoya Friday, April 29, 2016 1:38 PM
    Thursday, April 28, 2016 11:39 PM
    Owner