Pass Parameters to SQL Queries

[tecumseh]

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

[tecumseh · Accepted solution]

Created the ODBC Connection to Google Big Query and configured it per Admin. Added a simple SQL Statement to the ODBC Query in PQ.
Had already created a parameter table and I brought in Begin_Date as a date parameter in Power Query.
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 the parameter table.

[lbendlin]

Are you trying to supply lists of values?  You need to distinguish between single item parameters and list type parameters, and modify your SQL accordingly.