Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query–Part 2

In part 1 of this series I showed how you can set properties for Power Query function parameters so that saw a description, an example value and a dropdown list containing allowed values when they invoked the function. In this post I’ll show a slightly more complex use of this functionality: querying a relational database.

Consider the following function. It connects to the DimDate table in the Adventure Works DW sample database for SQL Server, removes all but the Date, Day, Month and Year columns, and allows you to filter the rows in the table by day, month and/or year.

//Declare function parameters
(optional DayFilter as text,
 optional MonthFilter as text, 
 optional YearFilter as number) => 
let
    //Connect to SQL Server
    Source = Sql.Database("localhost", "adventure works dw"),
    //Connect to DimDate table
    dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
    //Only keep four columns
    RemoveOtherColumns = Table.SelectColumns(dbo_DimDate,
                         {"FullDateAlternateKey", 
                          "EnglishDayNameOfWeek", 
                          "EnglishMonthName", 
                          "CalendarYear"}),
    //Rename columns
    RenameColumns = Table.RenameColumns(RemoveOtherColumns,{
                          {"FullDateAlternateKey", "Date"}, 
                          {"EnglishDayNameOfWeek", "Day"}, 
                          {"EnglishMonthName", "Month"}, 
                          {"CalendarYear", "Year"}}),
    //Filter table by Day, Month and Year if specified
    FilterDay = if DayFilter=null 
                   then 
                   RenameColumns 
                   else 
                   Table.SelectRows(RenameColumns, each ([Day] = DayFilter)),
    FilterMonth = if MonthFilter=null 
                     then 
                     FilterDay 
                     else 
                     Table.SelectRows(FilterDay, each ([Month] = MonthFilter)),
    FilterYear = if YearFilter=null 
                    then 
                    FilterMonth 
                    else 
                    Table.SelectRows(FilterMonth, each ([Year] = YearFilter))
in
    FilterYear

 

When you invoke the function you can enter the parameter values:

image

…and you get a filtered table as the output:

image

The obvious thing to do here is to make these parameters data-driven, so that the user can pick a day, month or year rather than type the text. Using the technique shown in my previous post, here’s how:


let

//Get the whole DimDate table
GetDimDateTable =
let
Source = Sql.Database("localhost", "adventure works dw"),
dbo_DimDate = Source{[Schema="dbo",Item="DimDate"]}[Data],
RemoveOtherColumns = Table.SelectColumns(dbo_DimDate,
{"FullDateAlternateKey",
"EnglishDayNameOfWeek",
"EnglishMonthName",
"CalendarYear"}),
RenameColumns = Table.RenameColumns(RemoveOtherColumns,{
{"FullDateAlternateKey", "Date"},
{"EnglishDayNameOfWeek", "Day"},
{"EnglishMonthName", "Month"},
{"CalendarYear", "Year"}})
in
RenameColumns,

//Create a function to filter DimDate
BaseFunction = (
optional DayFilter as text,
optional MonthFilter as text,
optional YearFilter as number) =>
let
FilterDay = if DayFilter=null
then
GetDimDateTable
else
Table.SelectRows(GetDimDateTable, each ([Day] = DayFilter)),
FilterMonth = if MonthFilter=null
then FilterDay
else
Table.SelectRows(FilterDay, each ([Month] = MonthFilter)),
FilterYear = if YearFilter=null
then
FilterMonth
else
Table.SelectRows(FilterMonth, each ([Year] = YearFilter))
in
FilterYear,

//Set AllowedValues on each parameter
AddAllowedValues =
let
AvailableDays = Table.Column(GetDimDateTable, "Day"),
AvailableMonths = Table.Column(GetDimDateTable, "Month"),
AvailableYears = Table.Column(GetDimDateTable, "Year"),
DayParamType = type nullable text
meta [Documentation.AllowedValues = AvailableDays],
MonthParamType = type nullable text
meta [Documentation.AllowedValues = AvailableMonths],
YearParamType = type nullable number
meta [Documentation.AllowedValues = AvailableYears],
NewFunctionType = type function (
optional DayFilter as DayParamType,
optional MonthFilter as MonthParamType,
optional YearFilter as YearParamType)
as table,
CastToType = Value.ReplaceType(BaseFunction, NewFunctionType)
in
CastToType

in
AddAllowedValues

 

It’s a big chunk of code, but not too difficult to follow I hope. The outer let statement has three steps inside it, each of which itself consists of a let statement. The first two steps, GetDimDateTable and BaseFunction, contain more or less the same code as the original function when put together. GetDimDateTable returns the whole DimDate table with the three columns I need; BaseFunction defines the function to filter it. The reason I split the code across two steps is so that, in the third step (AddAllowedValues) I can call the Table.Column() function on the Day, Month and Year columns returned by GetDimDateTable and get lists containing all the distinct values in these three columns. I’m then using these lists when setting AllowedValues in my custom types. The only other thing to point out here, that wasn’t mentioned in my previous post, is that for optional parameters the custom type used needs to be marked as nullable; this means you get the option to not pick anything in the dropdown box that is displayed.

Now, when you invoke this query, you see the dropdown lists populated with all the available values from the appropriate column in the DimDate table in SQL Server:

image

I also checked to see whether Query Folding takes place for this query, and I was relieved to see it does. Here’s the SQL generated by Power Query for a typical call to the function:

select [_].[FullDateAlternateKey] as [Date],
    [_].[EnglishDayNameOfWeek] as [Day],
    [_].[EnglishMonthName] as [Month],
    [_].[CalendarYear] as [Year]
from 
(
    select [_].[FullDateAlternateKey],
        [_].[EnglishDayNameOfWeek],
        [_].[EnglishMonthName],
        [_].[CalendarYear]
    from 
    (
        select [_].[FullDateAlternateKey],
            [_].[EnglishDayNameOfWeek],
            [_].[EnglishMonthName],
            [_].[CalendarYear]
        from 
        (
            select [FullDateAlternateKey],
                [EnglishDayNameOfWeek],
                [EnglishMonthName],
                [CalendarYear]
            from [dbo].[DimDate] as [$Table]
        ) as [_]
        where [_].[EnglishDayNameOfWeek] = 'Sunday'
    ) as [_]
    where [_].[EnglishMonthName] = 'August'
) as [_]
where [_].[CalendarYear] = 2003

 

You can download the sample workbook containing the code shown in this post here.

2 responses

  1. Very cool technique. Wishing I could share the query with my organization via the Data Catalog, giving the users the option to choose the values for each parameter. Unfortunately, when I share the query the parameters become opaque to others, and the query only returns the values which were loaded when I originally published the query, i.e. there’s no option for users to ‘invoke’ with different query values. Frustrating, as this renders this technique a local option only, and not something I can socialize across the organization.

  2. Pingback: Multiselect, Filtering And Functions In Power Query | Chris Webb's BI Blog

Leave a Reply

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

%d bloggers like this: