Closures, metadata and cascading parameters

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:

  1. Where to set the allowed values for function parameters
  2. How to build a function inception with higher order functions
  3. How to access a variable outside its scope
  4. And finally get to a result which mimics cascading selections

demoCascading

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:

FunctionDocumentationMetadata

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

4 thoughts on “Closures, metadata and cascading parameters

  1. Nice work! Now, if only we could have multi-select parameters… I know you can pass a list to a parameter, but really what I want is to give a user a dropdown box where they can select more than one item for a parameter easily.

    Like

    1. Thanks Chris! Interesting challenge.
      It all depends on the details of what is considered a multi select in your case.
      Here are a few options that I can think of:
      [in line with this post]
      1. Have a function with a bigger number of parameters which are decorated with identical lists (query results) for allowed values and on function invocation do a distinct for the selection and treat that as a “multi select”
      2. Similar approach to the blog post, only with two parameters, one with initial selection list, which gets reduced on each invocation, and the second one that works as a flag which stops accumulating the user selections and filters the actual data source. This option I imagine can become pretty dirty in no time by trashing the environment with #”Invoked Function”N queries.
      [in line with what you describe in excel]
      3. A combination where you load all the available choices into a table. Which you transform into a “Enter Data Formula Data” table. (I’ve got a function that does just that Table.EnterDataFormula https://github.com/Hugoberry/PowerQueryExtensions). This requires a manual step Ctrl+c Ctrl+v into a new query.
      Afterwards let the user delete the unnecessary records from this table, and use its values as the source for the final filtering.
      If not for the copy-paste there in the middle, I would say that this option has some potential 🙂

      Like

      1. What I want is proper, built-in support for multi-select parameters (ie an option in the UI that turns a parameter into a multi-select parameter that has checkboxes and returns a list) without any of the clever workarounds you’ve listed. I’ve implemented something like option #3 with a function that takes a list (eg https://blog.crossjoin.co.uk/2014/12/05/power-query-and-function-parameters-of-type-list/) and while it works fine, most users just can’t understand how to use it.

        Liked by 1 person

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