Forum Discussion
Pass Parameters to SQL Queries
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
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.
2 Replies
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.
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.
