Parameterized SQL Query

[Anonymous]

Hello, 

Is there a way wherein we can have paramterized SQL queries? Baiscally a way to run the SQL query for a specific filter condition rather than the running it for the entire population? 

For example: Let's say I have employees table, instead of importing the entire employees data (SELECT * FROM EMPLOYEES) can I only import the data for a specific employee based on its Employee_ID entered by the user (via slicer or any kind of prompt filter)? So that the SQL would run only for that specific Employee_ID ( SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 'XYZ') and take less time to execute. 


Thanks, 
Jash.

[edhans · Accepted solution]

Yes. Have you seen this article? Your query in Power Query can use this parameter, you just have to ensure that all steps up to and including the point where it filters is "folded" meaning Power Query or the service will generate the SQL statement and just get back the requested data.

You can also do this using quries in Power Query that return a single value (text, number, integer, date, etc) and use that query result as a filter. For example, the following query would return the earliest date from a table.

let
    Source = #"qryOpen Apply To Nos",
    #"Calculated Minimum" = List.Min(Source, [Date])
in
    #"Calculated Minimum"

 As long as that step folds you could use that query as a filter for your table to only get dates  on or after the date that query calculated. That isn't technically a parameter as defined by Power BI, but it is fully dynamic vs true Parmeters which have to be keyed in in the service.

This article has a much fuller description of how I create dynamic date tables based on the earliest and latest dates in my data by using these pseudo-parameters, and in SQL, it is 100% folded the way I do it, so the server does all of the heavy lifting.