If you’re interested in cascading parameters and Power BI custom connectors is an option for your project, you should give a try to Navigation Tables. Which soon enough could make their way into M standard library. If this is not an option, you might find your solution in the next few scrips tangled between functional programming concepts. You will also learn about:
- Where to set the allowed values for function parameters
- How to build a function inception with higher order functions
- How to access a variable outside its scope
- And finally get to a result which mimics cascading selections
Documenting the parameters
Weirdly enough, there isn’t much documentation on how to document your functions. Except Chris’ and Imke’s blogs this information is scattered across various forums and Q&A sites. [*]
For the purpose of our exercise, we’ll need to know how to decorate a function parameter with a list of allowed values. This list is stored as a metadata tag on the parameter data type within the function type (of the function).
Here is an illustrative diagram on how to interpret the previous statement:
The best way to add the AllowedValues list to a function is by building the chain above and assigning it to an already defined function by means of Value.ReplaceType. I will be using the following function (setParameters) that does just that.
Higher order function
In Power Query a function can return another function as a value. This is called a Higher Order Function (HOF). We’ve already seen an example of a HOF in the previous paragraph — Value.ReplaceType. Although sometimes you can get to an unusual notation when you deal with HOF (see below), they come very handy when you need to decorate a function definition dynamically. Or in our case dependent on user selection.
Lets start with a function fn, which returns the value of the parameter x.
Say we want the allowed values for parameter x to be the digits 1 to 9. Because we can’t modify the function definition of fn from within its scope, we’ll have to create a HOF that returns a copy of fn with decorated parameter x. Call it makeClosure.
If you’ve rushed and invoked makeClosure with a parameter selection, you must have noticed that the parameter values were indeed {1..9}, however not much cascading is going on as the execution of this function returned only your selection.
To get to the double-hop of the cascading functionality we’ll have to wrap the call in makeClosure into another HOF, that takes as a parameter the selection of x from the previous call and returns again a copy of the original function fn but with a filtered down list for the allowed values of x.
This time around, when making a selection on makeClosure and clicking invoke, you’ll be presented with another dialog with already preferred values for x. Cascading in action!
Closures
Another important feature of cascading parameters is making a final invocation dependent on both of the previous selections. This functionality can be achieved by means of closures.
Functions in Power Query have access to at least two environments, the global environment visible via #shared and the local environment, which is the union of parameters and local variables. When you define a function inside another function, the enclosed function can access the variables of the outer function. A HOF in which the returned function references the variables of the HOF local environment is called a closure.
Closures turn out to be a great way to remember environments that existed at the time they were created. Lets follow an example:
addTo is a HOF that returns the function (y) => y+x . Although this function doesn’t have a local variable x, on invocation it will read it from the environment of addTo. Thus addToFive is nothing but the original addTo function with stored environment [x=5].
Getting back to our original example, in order to call the function fn with a previous selection, we’ll have to convert it to a closure. For the sake of simplicity I assume the final operation is multiplication.
Subsequently, the only additional change to the function that triggers the cascading selection (makeClosure), is to pass the first selection of x as a parameter to fn. This way we are modifying the definition of fn with a filtered selection of x, and also are passing it as part of the calling environment.
Final notes
Although the examples that I followed in this post are trivial and involve only filtering digits and perform multiplication, the solution can easily be adapted to perform database calls and/or web requests. And of course more than one parameter cascading.
You can read the original discussion that inspired this solution here.
* [Update] While browsing through Data Connectors repository found this useful documentation as well: Adding Function Documentation
