Forum Discussion
How do I pass parameters to my SQL statement?
I got it to work. Here is my code ( DateBegin & DateEnd parameters have been set to 'text'):
let StartDate=DateBegin, EndDate=DateEnd, Source = Sql.Database("this-is-the-database-url.com", "TableNameHere", [Query="SELECT * FROM [TableNameHere].[dbo].[Logs] WHERE CreatedTime >='" &StartDate& "' AND CreatedTime <='" &EndDate& "' ", CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true]) in SourceOddly enough, I got a bunch of error messages when exiting the 'Query Editor' (as show in my previous post) but when I created everything again from scratch, it works perfectly.
I then saved the Power BI document as a template. When you open the template, you get prompted to enter the "DateBegin" and "DateEnd" parameters. Enter the parameters and the SQL query gets made, voila! :cathappy:
Greg_DecklerAnonymous I was actually able to do what I set out to do with the parameters, I just didn't know the correct syntax. I used the method described in “Passing Parameters To SQL Queries With Value.NativeQuery() In Power Query And Power BI” but just simply defined the variables as "DateBegin=DateBegin, DateEnd=DateEnd". It works great within the query editor but once I hit 'Close and apply', I get this error message:
I tried to find some solutions to overcome these but I wasn't able to find anything useful. I don't really understand what the first error message means.
Trying to declare the @DateBegin variable with the SQL DECLARE and SET methods only resulted in an error message saying "@DateBegin has already been declared".
Any ideas on how to get forward with these issues would be appreciated :smileyhappy:
I got it to work. Here is my code ( DateBegin & DateEnd parameters have been set to 'text'):
let
StartDate=DateBegin,
EndDate=DateEnd,
Source = Sql.Database("this-is-the-database-url.com", "TableNameHere",
[Query="SELECT * FROM [TableNameHere].[dbo].[Logs] WHERE CreatedTime >='" &StartDate& "' AND CreatedTime <='" &EndDate& "' ",
CommandTimeout=#duration(0, 0, 10, 0), HierarchicalNavigation=true, MultiSubnetFailover=true])
in
SourceOddly enough, I got a bunch of error messages when exiting the 'Query Editor' (as show in my previous post) but when I created everything again from scratch, it works perfectly.
I then saved the Power BI document as a template. When you open the template, you get prompted to enter the "DateBegin" and "DateEnd" parameters. Enter the parameters and the SQL query gets made, voila! :cathappy:
hey ,
where you define
CreatedTime >='" &StartDate& "'
This assuming the StartDate is text,
How would i do it for an integer in my below statement
Where [Store No]= '" &Branch& "'"
My Store Nos are all integer
i currently get error message :
Expression.Error: We cannot apply operator & to types Text and Number.
Please convert data type using Number.ToText(param)
Here's an example I got working using the AdventureWorks DB.
I created a sproc that accepts one int parameter. I created a parameter in PowerBI of type ANY called IDIN.Here's my query:
= Sql.Database(".\MSSQLSERVER2016", "AdventureWorksDW2014", [Query="Exec dbo.testfrompbi "&IDIN&"", HierarchicalNavigation=true])
It's been a long time since I last touched this. I don't remember the syntax off the top of my head but did you try to mess around with the " " surrounding the parameter statement? Try removing them?
the column of data i was working with in my table had the numbers saved as 'FLOAT' format, i was able to convert them to propper integers and it now worked, appreciate your reply though, Thank You,
What editor in Power BI were you using to enter this code?
I want to do this for my MYSQL data and pass parameters through my query code.
Please let me know.
Thanks.I have tried passing parameters in SQL statment. It is working for me.
But when i publish it to Power bi service, where do i have to input the parameter value?
I just have to say, THANK YOU GOOD SIR! your a life saver. serously spent 3 hours trying to solve this problem.. went through maybe 20 solutions and vidoes. And where I ended up was syntax errors because everyone says to type variables as &variable when it should be &variable&. This is actually for excel but the same applies.
Thank you so much !
I have been racking my brain half the day to make my StartDate and EndDate custom functions readable directly in the Source Query.
When I use a SQL statement directly in the Database lookup, I can write BETWEEN '2022-12-01' AND '2022-12-31', and it works perfectly! But this has to be done in the database connection directly.
Once i tried editting it directly in the Source Query in PowerQuery - nothing seemed to work. But deleting BETWEEN and rewriting to PowerQuery logic like yours using WITH AND instead - worked just fine now!
Thank you 😄
