How To Load Data In Power BI With Stored Procedure and Parameters

In this blog let's see how we can combine Stored Procedure and Parameters to load data into PBI.

First we need to create a very basic Stored Procedure like the following one:

Here is what this Stored Procedure returns:

A date column with Sales Amount

Now we need to go to Power Query in either Power BI, Power Pivot, SSAS or Dataflows in short where ever Power Query is available.

You need to create 2 parameters, StartDate and EndDate as shown below.

Now import the data from SQL Server by executing the Stored Procedure as shown below:

Now Power Query will load a preview with the M code containing the code of the Stored Procedure.

We need to modify this code by going to Advanced Editor, break the code into several lines and instead of the hard coded dates use the parameters with Text.From function.

Once done click on Edit Permission to grant the permission to Power Query to execute the Stored Procedure against the database.

And now Power Query will load the data.