Power Query and SQL Server Stored Procedures

Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could pass parameters to a stored proc from Power Query and show the results in Excel. But, I couldn’t find that much information around this as I expected. So, I decided to do some work around and you can read the results in this post. To simplify the solution, I’m going to use uspGetBillOfMaterials stored procedure in AdventureWorks 2012 database. The stored procedure accepts an integer number as ProductID and a date as CheckDate. So we need to pass two parameters to uspGetBillOfMaterials  to get the results.

If we execute the stored proc in SSMS using

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

, we’ll get the following result:

Now, lets go to do some works on Power Query. So open Microsoft Excel and go to Power Query tab and select SQL Server database.

Now type Server, Database and SQL Statement, then click OK.

Select a name for the query, I names it GetBOM. Then from Home tab click on “Close & Load”.

So far we’ve loaded the results of to Excel. Now we need to pass the parameters to the stored proc. As you can see, the above stored proc  accepts an integer and a date as parameters. So we create a table in the Excel sheet with two columns that contain the parameters. Name the table as “param”.

To make out life easier I changed the format cell of the “Check Date” column to Text, other wise we’ll need to convert it in Power Query. We still need to convert ProductID in Power Query.

Now go back to Power Query, right click on GetBOM and click Edit

In GetBOM Query Editor window, go to View tab and click “Advanced Editor”.

Here we need to add some codes. The scripts in Power Query are written in a language called “M”.

All we need is to parameterise the query so that we read the contents of from the “param” table we defined before. In M language, to read a cell content we need to address the table as below:

Excel.CurrentWorkbook(){[Name=”TABLE_NAME“]}[Content]{ROW_NUMBER}[#”COLUMN_NAME”],

In the above code, TABLE_NAME is “param” in our sample, ROW_NUMBER is the number of row that we need to load its content and COLUMN_NAME is the name of the column. So to adders the value of the first column of “param” table, the above code will be as below:

Excel.CurrentWorkbook(){[Name=”param“]}[Content]{0}[#”ProductID”],

and for the second one it will be like this:

Excel.CurrentWorkbook(){[Name=”param“]}[Content]{0}[#”Check Date”],

Now we need to replace the constants from the query with the expressions above to make the query parameterised.  You can copy the code below in the Advanced Editor:

let
    ProductID=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”ProductID”],
    CheckDate=Excel.CurrentWorkbook(){[Name=”param”]}[Content]{0}[#”Check Date”],
    Source = Sql.Database(“SQL_SERVER_INSTANCE NAME“, “AdventureWorks2012”,
    [Query=”exec [dbo].[uspGetBillOfMaterials] ‘”
    & Number.ToText(ProductID)
    & “‘, ‘”
    & CheckDate
    & “‘”])
in
    Source

You need to put your own SQL Server instance name in the above code.

Note to the single quotation marks in the code.

To concatenate texts we use “&” in M language. Click Done then click Close and Load from Home tab.

Now if you change the values of the “param” table and refresh data you’ll see the new results in Excel.

For instance, change the ProductID from 727 to 800 then refresh data. You’ll see the below screen:

As you can see the first parameter to pass to stored procedure is changed to 800. Click RUN to see the results in Excel.

We are done!