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:
![](https://static.wixstatic.com/media/a3da57_2a68ee2e4f8b4227b433cd0bf7dc910f~mv2.png/v1/fill/w_49,h_30,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_2a68ee2e4f8b4227b433cd0bf7dc910f~mv2.png)
Here is what this Stored Procedure returns:
A date column with Sales Amount
![](https://static.wixstatic.com/media/a3da57_00f42373918d44e896a43f35aee2e3ab~mv2.png/v1/fill/w_49,h_30,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_00f42373918d44e896a43f35aee2e3ab~mv2.png)
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.
![](https://static.wixstatic.com/media/a3da57_a2b2b973e7774e398a6fdc9e6c61c6f4~mv2.png/v1/fill/w_49,h_39,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_a2b2b973e7774e398a6fdc9e6c61c6f4~mv2.png)
Now import the data from SQL Server by executing the Stored Procedure as shown below:
![](https://static.wixstatic.com/media/a3da57_0c36cfdb1e3541ab8f2099f9a7a0799f~mv2.png/v1/fill/w_70,h_56,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_0c36cfdb1e3541ab8f2099f9a7a0799f~mv2.png)
Now Power Query will load a preview with the M code containing the code of the Stored Procedure.
![](https://static.wixstatic.com/media/a3da57_a592995732f94c42ac45e1e2aef84878~mv2.png/v1/fill/w_49,h_21,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_a592995732f94c42ac45e1e2aef84878~mv2.png)
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.
![](https://static.wixstatic.com/media/a3da57_dfbd458e1f6546b08a4675c94ac7aea4~mv2.png/v1/fill/w_48,h_22,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_dfbd458e1f6546b08a4675c94ac7aea4~mv2.png)
Once done click on Edit Permission to grant the permission to Power Query to execute the Stored Procedure against the database.
![](https://static.wixstatic.com/media/a3da57_c25847ede2784680ae5c8c49e266d352~mv2.png/v1/fill/w_49,h_14,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_c25847ede2784680ae5c8c49e266d352~mv2.png)
![](https://static.wixstatic.com/media/a3da57_813373f93c724c33a7a75080eddbdf17~mv2.png/v1/fill/w_55,h_51,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_813373f93c724c33a7a75080eddbdf17~mv2.png)
And now Power Query will load the data.
![](https://static.wixstatic.com/media/a3da57_39e11e8d6f1d48809dd211359c0f72f3~mv2.png/v1/fill/w_49,h_21,al_c,q_85,usm_0.66_1.00_0.01,blur_2,enc_auto/a3da57_39e11e8d6f1d48809dd211359c0f72f3~mv2.png)