TSQL Table-Valued Functions And Dynamic M Parameters In Power BI DirectQuery

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!

8 responses

  1. Pingback: Table-Valued Functions and Dynamic M Parameters – Curated SQL

  2. Pingback: Power BI, les nouveautés de ce mois de février 2022 « Power BI & Excel by Mehdi

  3. Thanks Chris. Do you know if PBI uses query folding on custom sql (on all sources) ? If it does and it’s smart i.e. detects a single SELECT statement and adds or expand the WHERE predicate, table-value functions (w or w/o M parameters) has another big advantage over stored procedures as they could be query folded i.e. “CALL MyProc(MyParam) WHERE year=2005” is not possible.
    Not relevant in your example use but consider a table function that returns parametrized) 1M rows…

    • Answering for Google BigQuery, for which the PBI/PQ connector supports M parameters for some time, … it unfortunately does NOT support query-folding on custom/native SQL. Forcing for it does not help.
      = Value.NativeQuery(GoogleBigQuery.Database([Data], “select * from tmp.table_func(‘foo’, ‘bar’)”, null, [EnableFolding=true])
      Next step, filtering on the year in PQ will still load all and filter locally.

      • Not everything can be folded in Import mode when you use a native SQL query. However, if you’re talking about DirectQuery (which we are here) on a fairly simple select statement I would expect to see an error message rather than “local filtering” taking place, which I’m pretty sure isn’t even possible in DirectQuery mode for this kind of scenario. How do you know query folding isn’t taking place?

      • I assumed based on the fact that in PQ, the filtering step has “View Native Query” grayed out, and yes in DQ mode. However looking at the super useful “Diagnose” output, I do see indeed an Execute Command with the folded query ! Conclusion, if not obvious (e.g. response time or ‘view native query’ option enabled), look at the diagnostics or in the logs on the data source side.
        Having query-folding + M-parameters with table functions allows to push even more to the rdbms – for us, that’s using the most out of BigQuery BI Engine (in-mem).

  4. Can this be used to in a native query? Current having an issue making this works. Followed: Dynamic M query parameters in Power BI Desktop to set up parameter (have the preview option selected), parameter set up as Date/Any Value/Required. Created a table w/ the a rolling 30 days in DAX, and bound the column to my parameter. Added the Parameter to my direct TSQL query and it works with the parameter using the ‘current value’ in the parameter

    Added date column that is bound to the parameter to a slicer. When I click on a new date in the slicer and hit refresh the data that is return is the same. It seems like the change in the slicer isn’t being reflected in the parameter. If I manually change the parameter using ‘edit parameter’ the data will update to the new date. Is there a setting that I am missing?

Leave a Reply

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

%d bloggers like this: