Pass Parameters to SQL Queries

[wsnyder]

Hi all,
I am trying to pass parameters to a SQL Query

I found this article by Chirs Webb

I have parameters in Power Query from a Parameter Table on my worksheet. Both parameters show as a calendar icon in Power Query

I set up a Test Database on SQL Server and entered 3 dates and sales amount
1/31/2023, 200
2/1/2023, 100
2/28/2023, 300

I then edited Chris's code based on my database and parameters, but nothing was returned - no data, errors, or messages.
Any thoughts on what I'm doing wrong?

let
    Source = Sql.Database("localhost", "Yeti"),
    Test = Value.NativeQuery(
            Source,
            "SELECT * FROM sales
            WHERE salesdate >= @start
            AND
            salesdate <= @stop",
            [start=Begin_Date , stop=End_Date])
in
    Test

Thanks,
-w

 

[wsnyder]

Bump.

 

[wsnyder]

I finally resolved.

I created the ODBC Connection to Google Big Query and configured it per Admin. I added a simple SQL Statement to the ODBC Query in PQ.
I had already created a parameter table and I brought in Begin_Date as a date parameter in Power Query.
I opened the advanced editor and edited the SQL String as this:

Source = Odbc.Query("dsn=GoogleBigQuery", "#standardSQL#(lf)#(lf)select *#(lf)#(lf)from `server.database.table`#(lf)#(lf)where date between '" & Date.ToText(Begin_Date,"YYYY-MM-DD") & "' and '" & Date.ToText( Date.EndOfMonth(Begin_Date),"YYYY-MM-DD") & "'"),

Where Begin_Date is the name of the date parameter coming from my parameter table.