Overview

Sometimes, there is a situation when we need to get the data by consuming SQL Server Stored Procedure. SQL Server Stored Procedures have parameters that we need to pass dynamically.

Power BI provides functionality to execute a Stored Procedure using Managed Parameters.

In this article, we will talk about the following.

Limitation

This feature will work only for Import Mode.

Example

I have one procedure in SQL Server named “sp_getEmpshiftDetails” which has two parameters named “vStartDate” and “vEndDate”. I want to use this procedure and load the data into Power BI Desktop. I have attached the file with this article for practice purposes.

So, now let’s get started!

Step 1. Create Manage Parameter in Power BI Desktop.

  1. Open Power BI Desktop and from the Home tab, select “Edit Queries”.
    Parameter
  2. Click on “Manage Parameters” and select “New Parameter”.
    New Parameter
  3. It will open a popup to create a new parameter. Select “New”.

It will ask for the following information.

I created parameters “vStartDate” and “vEndDate”, as shown in the screenshot.

Pararmeters

Step 2. Load (Execute Stored Procedure).

  1. Now, from “Home”, select “New source”.
    New source
  2. Select “Databases”, select “SQL Server Database”.
    SQL Server
  3. Fill in the required fields and in the command window use the below line to execute the procedure. 
    EXEC sp_getEmpshiftDetails '2015-06-23','2015-06-25'  
    OK
  4. It will preview the data. Click on “Load”.
    Load

Step 3. Change Query in Advance Editor

Select Query and click on “Advanced Editor”.

Advance Editor

Replace the existing query with a new query.

let
    SQLSource = (vStartDate as date, vEndDate as date) =>
    let
        Source = Sql.Database("DHRUVIN\SQLEXPRESS", "WMS_201", [Query="EXEC sp_getEmpshiftDetails '" & Date.ToText(vStartDate) & "','" & Date.ToText(vEndDate) & "' #(lf)#(lf)#(lf) #(lf)"])
    in
        Source
in
    SQLSource

The below screenshot shows a comparison of both queries.

Screenshot

Step 4.Invoke Result

Conclusion

Now, I hope you have got a better idea of “Managed Parameters” in Power BI. We can pass the dynamic parameters to SQL Server Stored procedures using this feature. Try this on your own and share your opinion with me.