My favourite – and it seems many other people’s favourite – new feature in the February 2022 Power BI Desktop release is support for more datasources (including SQL Server, Azure SQL DB and Synapse) with dynamic M parameters. In my opinion dynamic M parameters are extremely important for anyone planning to use DirectQuery: they give you a lot more control over the SQL that is generated by Power BI and therefore give you a lot more control over query performance.
Teo Lachev has already stolen my thunder and blogged about how the new functionality allows you to use a TSQL stored procedure as the source of a table in DirectQuery mode. In this post I’m going to show you something very similar – but instead of using a stored procedure, I’m going to show a simple example of how to use a TSQL table-valued function, which I think has a slight advantage in terms of ease-of-use.
To start off I created a table-valued function in the Adventure Works 2017 sample database on my local SQL Server which returns some filtered data from the DimDate table:
CREATE FUNCTION [dbo].[udfDates] (
@day_name varchar(50),
@month_name varchar(50)
)
RETURNS TABLE
AS
RETURN
SELECT
FullDateAlternateKey, EnglishDayNameOfWeek, EnglishMonthName, CalendarYear
FROM
DimDate
WHERE
EnglishDayNameOfWeek=@day_name
and
EnglishMonthName=@month_name;
Here’s how it can be called in a SQL SELECT statement:
select
FullDateAlternateKey,
EnglishDayNameOfWeek,
EnglishMonthName,
CalendarYear
from
udfDates('Thursday', 'February')
where
CalendarYear=2005

Next, I created a Power Query query in my dataset that called this function and returned a table in DirectQuery mode. The great thing about table-valued functions is that they appear in the Navigator pane when you connect to a SQL Server database from the Power Query Editor:

You can only hard-code the values you pass to the function’s parameters at this point but even if you do nothing here you can just return an empty table. After having done this I selected DirectQuery mode:

After this I created two Power Query text parameters, called DayName and MonthName, to hold the month and day names to be passed to the function:


I also created two Import mode queries called DayNames and MonthNames to hold all the valid values for the DayName and MonthName parameters:


The last thing to do in the Power Query Editor was to edit the query calling the function to pass the M parameters to it. Here’s the M code for the query after the modification made to the “Invoked Functiondbo_udfDates1” step:
let
Source = Sql.Database(
"localhost",
"AdventureWorksDW2017"
),
dbo_udfDates = Source
{
[
Schema = "dbo",
Item = "udfDates"
]
}
[Data],
#"Invoked Functiondbo_udfDates1"
= dbo_udfDates(DayName, MonthName)
in
#"Invoked Functiondbo_udfDates1"
At this point I closed the Power Query Editor and loaded the three tables to my dataset:

Next I bound the EnglishDayNameOfWeek column on the DayNames table to the DayName M parameter and the EnglishMonthName column on the MonthNames table to the MonthName M parameter:


Finally, I built a report with two slicers bound to the two dynamic M parameter columns and a table showing the output of the table-valued function:

Here’s an example of the TSQL generated by Power BI to populate the table in this report:
SELECT
TOP (501)
[t0].[FullDateAlternateKey],
[t0].[EnglishDayNameOfWeek],
[t0].[EnglishMonthName]
FROM
(
(
select [$Table].[FullDateAlternateKey],
[$Table].[EnglishDayNameOfWeek],
[$Table].[EnglishMonthName],
[$Table].[CalendarYear]
from [dbo].[udfDates]('Thursday', 'February') as [$Table]
)
)
AS [t0]
WHERE
(
[t0].[CalendarYear] = 2010
)
GROUP BY
[t0].[FullDateAlternateKey],[t0].[EnglishDayNameOfWeek],
[t0].[EnglishMonthName],[t0].[CalendarYear]
ORDER BY [t0].[FullDateAlternateKey]
ASC
,[t0].[EnglishMonthName]
ASC
,[t0].[CalendarYear]
ASC
,[t0].[EnglishDayNameOfWeek]
ASC
I am by no means an expert in writing efficient TSQL so I can’t comment on the pros and cons of table-valued functions, stored procedures or using native SQL queries in Power BI (although the last of these has obvious maintainability issues). Hopefully, though, you can see the possibilities – and if you do get round to using this approach on a project, please let me know how you get on!