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.
[sourcecode language=”text” padlinenumbers=”true”]
//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
[/sourcecode]
When you invoke the function you can enter the parameter values:
…and you get a filtered table as the output:
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:
[sourcecode language=”text”]
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
[/sourcecode]
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:
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:
[sourcecode language=”text”]
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
[/sourcecode]
You can download the sample workbook containing the code shown in this post here.
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.
thanks. but your sample workbook containing the code link does not work anymore. could you please fix it.