Quick Tips, Power BI Desktop, Query Parameters, Part 4, Passing Power Query Parameter Values to SQL Server Stored Procedures

I have written 3 blogposts about query parameters in the past.

This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.

The solution is somewhat easy. I created a simple stored procedure in AdventureWorksDW2019 as below:

CREATE PROCEDURE SP_Sales_by_Date 
	@date int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT *
	FROM [dbo].[FactInternetSales]
	WHERE OrderDateKey >= @date
END
GO

In Power BI Desktop, get data from SQL Server, then:

  • Enter Server name
  • Enter Database name
  • Select Data Connectivity Mode
  • Expand the Advanced options
  • Type in a SQL statement to call the stored procedure like below:
exec SP_Sales_by_Date @date = 20140101
  • Click OK
Get Data From SQL Server using SQL Statements in Power BI Desktop
  • Click Transform Data
Transform Data in Power BI Desktop

Now we need to create a Query Parameter. In my sample I create a DateKey in Decimal Number data type:

Creating New Query Parameter in Power BI Desktop

At this point you have to modify the Power Query expression either from the Formula Bar or from Advanced Editor. We truncate the T-SQL statement after the @date, then we concatenate the Query string with the Query Parameter name. If the Query Parameter data type is not Text then we have to convert it to Text. In my sample, the data type is Number, therefore I use Text.From() function.

Here is a screenshot of the Power Query expression before changing the code:

Power Query Expressions in the Formula Bar in Power Query Editor in Power BI Desktop

The Power Query expression is:

Sql.Database(".\sql2019", "AdventureWorksDW2019", [Query="exec SP_Sales_by_Date @date = 20140101"])
  • Change the code as below:
Sql.Database(".\sql2019", "AdventureWorksDW2019", [Query="exec SP_Sales_by_Date @date " & Text.From(DateKey)])
  • Click Edit Permission
  • Click Run
  • That’s it. Here is the results:

We can use this method to parameterise any other T-SQL statements in Power BI with Query Parameters.

Enjoy!

2 thoughts on “Quick Tips, Power BI Desktop, Query Parameters, Part 4, Passing Power Query Parameter Values to SQL Server Stored Procedures

  1. Whatif my SP paramenter consists of a string of comma-separated values…how do I create a PBI query parameter that provides a list of comma-separated values to pass to stored procedure?

    1. Hi Robert,

      It is easy from a Power Query perspective.
      You only need to create a Query Parameter with type Text.
      Then pass the comma-separated values to the parameter.
      That said, the heavy lifting is more on SQL Server side though.
      In SQL Server, you require to split the string into values.
      Read here how to do so.

      Hopefully that helps,
      Cheers

Leave a Reply

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


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