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:

image

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.

image

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

image

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

image

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”.

image

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.

image

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

image

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

image

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.

image

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:

image

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

image

We are done!

11 thoughts on “Power Query and SQL Server Stored Procedures

  1. You are brilliant! I really enjoy reading your blog. Can you share some insight into cleaning/preparing data from ERP/OLTP db and load it into fact tables efficiently?

  2. My question would be if you can do the opposite. Create a stored procedure, that then are using DAX to create a Excel file?

    Bests

  3. Hi Soheil,
    Thank you for such an insightful post. I’m loving your blog and have been using a lot of your posts.

    I’ve been trying to replicate your results in a native database query and not a stored procedure, but i’m not being successful at it.

    Basically i want to be able to do:
    let
    BeginDt = GetValue(rng_BeginDt),
    EndDt = GetValue(rng_EndDt),
    Source = Sql.Database(“XXX”, “YYY”, [Query=”

    SELECT
    SalesID

    FROM sales

    WHERE
    SalesDt BETWEEN ‘BeginDt’ AND ‘&EndDt&’

    “]

    GetValue is a function that i’ve defined to grab the value from the range name.

    I’ve tried different variations, like using &’s or also doing your method of calling the date without a function using the Excel.CurrentWorkbook method.

    I get the following error:
    DataSource.Error: Microsoft SQL: Conversion failed when converting datetime from character string.
    Details:
    Message=Conversion failed when converting datetime from character string.
    Number=241
    Class=16

    Any ideas or guidance would be extremely appreciated.
    Thanks again!
    David

  4. Great post!
    You wrote: To make out life easier I changed the format cell of the “Check Date” column to Text, otherwise we’ll need to convert it in Power Query
    I would like to pass to sql query the number that represents the date.
    In your example Check Date 2009-01-01 is 39814 in Excel and I would like to pass 39814 to my sql query.
    Could you please show required conversions in Power Query

  5. Thanks Soheil,

    Seems when we change the ProductID is returning more than the parameters. Even in your screen shot shows more Products than just than those with Product ID 800 specified in your parameters. Any thoughts?

    1. Hi Winston,

      Welcome to BIInsight.com and thanks for your feedback.
      Please note that the column shown in the screenshot is NOT ProductID, it is ProductAssemblyID which is a totally different ID.

      Power Query and SQL Server Stored Procedures

      The ProductID though is used in the filter.
      Hopefully that helps.

      Cheers.

  6. Does anyone know where I can find out how to pass multiple dates like:
    where Dates in (date1, date2, date3)
    In parameters? I can get it to work fine with:
    where Dates = ‘date1’
    The challenge for me is referencing multiple parameters while ensuring the integrity of the SQL remains in tact. (& Date1, &Date2, &Date3) does not work.

  7. Hi Soheil,
    This is a solid! Only thing supposed the parameter value has to come from different sheet example Parameters with a Table name tblParameters how does it look like? I tried but hit an error

    ProductID=Excel.Sheets(“Parameters”)(){[Name=”tblParameters ”]}[Content]{0}[#”ProductID”]

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.