Power Query and Stored Procedures with Parameters

A great question come up on the Power Query forum:

“I would like to know if there is a way to pass parameters to a Stored Procedure used within a Power Query.”

Here are the steps in detail that I was not able to supply in the forum reply.

The short answer to the above question is: “Yes, you can.”

This example will use the AdventureWorks2012 database with the following objects: [HumanResources].[Employee] table and the [dbo].[uspGetEmployeeManagers] stored procedure.

First, you need to return the [HumanResources].[Employee] in a Power Query within the workbook and add a table to use as the parameter to pass to the stored procedure. Here’s how to do this:

• Open an excel workbook, and click on the POWER QUERY tab. Then select the From Database drop-down button, and select From SQL Server Database.

  

• Fill in the Server and Database textboxes, and Click OK.

  

• In the Navigator window, double click the HumanResources.Employee table.

  

• The Query Editor will open. Hold down the control key and select every column except BusinessEntityID, LoginID, and OrganizationNode. Click the Remove Columns button. Click the Close & Load button to return to Excel.

  

  You now have a much more manageable table. You will only use this table for reference.

  

  Note: You could have easily added the SQL query to the Microsoft SQL Server connection dialog window.

  SELECT [BusinessEntityID], [LoginID], [OrganizationNode] FROM [HumanResources].[Employee]

• After you save the Power Query, a table will be created within Excel. To the right of the table, Add: BusinessEntityID to Cell E1 and 25 to cell E2. Highlight the two cells, and go to the INSERT tab. Click the table button to turn the two cells into a table. Go to the TABLE TOOLS Design tab, and change the name of the table to Parameter.

  

• With the Table still highlighted, select the POWER QUERY tab and click the From Table button. A new Power Query will be created.

  

• Click the Close & Load button to return to Excel.

The next step will create the connection to the stored procedure. Some small steps along the way will create multiple Power Queries for demonstration purposes.

• Create another Power Query like in step 1 above.
  • Server:
    Yourserver (I used localhost above)
  • Database: AdventureWorks2012
  • SQL Statement: EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 278

  

• Three rows will be returned. Name the Power Query SPwithNoParameter. Click the Close & Load button to return to Excel.

  

• Three Queries will be in the Workbook Queries window. Right click on the SPwithNoParameter Query, and select Duplicate.

  

• Double click on the new SPwithNoParameter (2), and open the Query Editor window and rename it to SPwithParameterAndExtra.

  Notice the M language statement within the Formula Bar.

  = Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 278”])

  You are going to have to modify this statement to take the value from the parameter table. But first, let’s take some small steps.

1. Modify the statement to add the following to the end of the statement: ” & Number.ToText(278) ])

= Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = ” & Number.ToText(278) ])

1. Click the Refresh Preview button. Nothing will change, and that’s good. (Small steps, remember.)

1. Now you have to reference the Parameter table from Excel. Replace the 278 with Parameter[BusinessEntityID]{0}

Note: The {0} at the end of the table syntax is limiting the return to the first row because the table could contain multiple rows and our stored procedure needs a scalar value and not a table object.

= Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = ” & Number.ToText(278) ])

• A Warning will come up and state that permission is required to run this native database query. Click the Edit Permission button, and then click the Run button.

  

  And with that you would be done, but the security guys are looking out for us. Power Query does not allow data from two different data source to be referenced within one step. So you get the following message.

  

  Now are you see my naming convention, “AndExtra”

• Click the Close & Load button to return to Excel.
• Right click on the SPwithParameterAndExtra Query and select Duplicate.
• Double click the query and rename the query to SPwithParameter.

  This time you will have to add more code to the M Query.

• Select the View Tab, and click the Advanced Editor.

  

• Add the following:

  SpParameter = Excel.CurrentWorkbook(){[Name=”Parameter”]}[Content], before the Source entry. Click Done.

  Note: This statement was from the Parameter query. I did have to name it SpParameter.

  

• Go to the Formula Bar, and modify the statement to point to the SpParameter.

  = Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = ” & Number.ToText(SpParameter[BusinessEntityID]{0}) ])

• One more security warning: Information is required about data privacy. Click Continue.

• Select Public, and click Save.

  

• Notice you have now selected only one row. Click Close & Load.

  

• Go to the HumanResources Employee query, and pick another ID. Take that ID and place it within the Parameter Query.

Summary

To review what was done in the steps above, you created a table (Parameter) that was used by another query (SPwithParameter) that was sourced from a stored procedure. You used the Parameter table to change the parameter of the stored procedure. After implementing all of these steps, you are able to update a table in your Excel document and have the results of other queries be driven off the modification in Excel.

The next logical question would be: Why go through all of this trouble?

If you are creating easy to use self-service BI projects, it seems easier to have a business user modify a table on an Excel document and not have to update the M code like above.

Mileage may vary, but the easier you make their experience, the more time you have for other cool stuff.