Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |