Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Adam_2024
Regular Visitor

How to connect a store procedure to power query and control its parameter through slicer in power BI

Hi All,

 

I am trying to connect a store procedure from SQL Server to Power Query and have an option to contol its parameters through Power BI Slicers. However, if I want to use binding parameters, I have to connect Store Procedure to Power Query at "Direct Mode". That is the reason I use  OPENROWSET . Everything looks good in Power BI Desktop, but when I publish it to service. Dashboard is unable to find its dataset. Any suggestion?

 

let
startStr = Text.From(START_PRAM),
endStr = Text.From(END_PRAM),
Source = Sql.Database("Prod64", "AAA_DWH", [
Query = "SELECT * FROM OPENROWSET('SQLNCLI', " &
"'Server=Prod64;Trusted_Connection=yes;', " &
"'EXEC [dwh].[RPT].[RPT_PO_VO] @START_DATE_SP=''" & startStr & "'', @End_DATE_SP=''" & endStr & "''')"
])
in
Source

2 REPLIES 2
Shivu-2000
Super User
Super User

Hi @Adam_2024

I could offer you two ways to solve the problem: 

1: Use DirectQuery with Parameters (Preview Feature)

  1. Enable DirectQuery with Parameters: This feature is still in preview, so you need to enable it in Power BI Desktop settings (File > Options and Settings > Options > Current File > Preview Features). Check the box for "DirectQuery with Parameters".
  2. Define Parameters in Power Query: In your existing code, replace the string concatenation for parameters with actual parameters. Here's an example:
let
  startParam = Parameter.Text("Start Date"),
  endParam = Parameter.Text("End Date"),
  Source = Sql.Databases(
    "Prod64"
  ){0}[dbo].[RPT_PO_VO](startDate = startParam, endDate = endParam)
in
  Source
  1. Publish to Power BI Service: This approach allows binding slicer selections to the defined parameters in Power BI Service.

2: Use a Staging Table 

  1. Create a Staging Table: In your SQL Server database, create a staging table with columns matching the stored procedure's output.
  2. Modify Stored Procedure: Modify the stored procedure to insert data into the staging table based on the input parameters.
  3. Power Query with Import Mode: In Power Query, connect to the staging table using Import mode.
  4. Slicer and Refresh: Create a slicer for your desired parameter (e.g., date range). Set up a scheduled refresh for the dataset based on your data update frequency.

You can try both of them and find which works for you.

If you find this insightful, please provide a Kudo and accept this as a solution.

Hi @Shivu-2000 Many

thanks for providing some advice,

I am happy to get more info regarding your first solution as I am curious to see whether I can query a store procedure by changing its parameter in Power BI Slicer like Tableau. 

My Power BI Desktop version is the latest one ( 2024 -  April). However, Preview feature is not availbe in Current File, instead in Global Section. I also looked for Preview feature in Global Section for DirectQuery with Parameters and there is no option available for this feature. Any suggestion?

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.