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!

9 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

    1. HI David,

      Thank you for your nice comments.
      I’m really happy to hear that you like my website.
      About your issue, I think it is clear from the error message that your SQL query retrieves string values, but, your function gets datetime.
      Am I right?
      If so, then you can simply convert your query results to bring you datetime instead of string.
      It could be something like:

      SELECT CAST(rng_BeginDt AS DATETIME) AS rng_BeginDt
      , CAST(rng_EndDt AS DATETIME) AS rng_EndDt
      FROM TABLE

      If your function gets especial date format then you can use CONVERT instead of CAST.
      You can read more about this here: (https://msdn.microsoft.com/en-nz/library/ms187928.aspx)

      Cheers

  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

    1. Hi Adam and welcome to biinsight.com.

      If you are using integer values rather than date in your stored procedure and you’re not willing/allowed to modify the stored procedure to convert the datetime input parameter to int the easiest way I can think of is adding a new cell into your excel worksheet.
      The for write this formula: =VALUE(DATE_CELL_VALUE).
      Change the format of the cell to number.
      To do so right click on the new cell-> Format Cells-> Number.
      Do not forget to set the decimal places to 0.
      Then pass the value of the new cell to the stored procedure.
      The rest of the procedure will be the same.

      Cheers.

Leave a Reply