cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SAG1 Helper I
Helper I

Re: How do I pass parameters to my SQL statement?

Hello CarlosDash 

 

I am in similar situation like this , i have huge DB that i can not download all together so need user to be able to select parameters and then get data ccordingly , how did you approach it ? Can you please help me here .

brandonmc
Regular Visitor

Re: How do I pass parameters to my SQL statement?

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])

 

 

smfaizhaider
Regular Visitor

Re: How do I pass parameters to my SQL statement?

@peterhinton

Please convert data type using Number.ToText(param)

manjirit Helper I
Helper I

Re: How do I pass parameters to my SQL statement?

All the tutorials and posts I saw were about how to pass the query parameter in the 'M' query in Power BI advance editor and nothing about how to actually pass a parameter by editing the souce query SQL statement. 

 

Any idea about that?

 

The main problem with adding the parameter in the Advanced Editor is that, once its edited there, then the setting wheel next the Source of the query disappears and there is no way to see or edit your SQL statement after that. 

dataviznz Helper II
Helper II

Re: How do I pass parameters to my SQL statement?

Any more insights on this? Does Power BI support passing a parameter to the SQL query?

bajaja2
New Member

Re: How do I pass parameters to my SQL statement?

Hey did you get any work around for the same?

 

I have also been working on Huge Set of tables and need to filter them based on the bases of date.

 

Please let me know is there a way to implement the same.

 

Appreciate the Help!

 

 

JasonRoy
Frequent Visitor

Re: How do I pass parameters to my SQL statement?

Step one is creating your custom SQL statement. I like to add my placeholder for a parameter when I create the SQL statements and replace those with the parameter once defined. The parameters need to be concatenated to the query using & outside of the quotes holding the query. If you created a table with a single SQL query, your M may look like the following.

 

Non-Parameterized Query

let
Source = Sql.Database("MyTestServer", "MyTestDatabase", [Query="SELECT test_key from MyFactTestTable WHERE datekey >= 20190101 AND datekey <= 20190201"])
in
Source

 

Parameterized Query

let
Source = Sql.Database("MyTestServer", "MyTestDatabase", [Query="SELECT test_key from MyFactTestTable WHERE datekey >= " & StartDate & " AND datekey <= " & CloseDate"])
in
Source

vacuesta Helper I
Helper I

Re: How do I pass parameters to my SQL statement?

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.

Highlighted
AishwariyaV Helper II
Helper II

Re: How do I pass parameters to my SQL statement?

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?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors