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.

• Power BI Desktop Query Parameters, Part 1, Introduction
• Power BI Desktop Query Parameters, Part2, Dynamic Data Masking and Query Parameters
• Power BI Desktop Query Parameters, Part 3, List Output

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

• Click Transform Data

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

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:

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!