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!

Any thoughts? Share it with us here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.