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 thoughts on “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query–Part 2

  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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s