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


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. 

(f as function, allowedValues as list)=>
//create a new parameter type with metadata for allowed values
parameterType = Value.Type(allowedValues{1}) meta [Documentation.AllowedValues = allowedValues],
//create a function type with custom parameter type
myFunType = type function (select as parameterType) as function
in //replace the orginal function type with the new myFunType
view raw setParameters.ts hosted with ❤ by GitHub

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. 

ring = () => () =>[Answer="Hello"],
firstCall = ring(), //returns a function
secondCall = ring()() //returns Answer="Hello"

Lets start with a function fn, which returns the value of the parameter x.

(x) => x
//do filtered operations on x
view raw fn.ts hosted with ❤ by GitHub

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.

fnCopy = setParameters(fn,{1..9})
in fnCopy
view raw makeClosure.ts hosted with ❤ by GitHub

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.

//overwrite function type for _fn_ with a parameter list limited to x
fnCopy = (x) => setParameters(fn,{1..x})
in //wrap the previous overwrite in another function call with allowed parameters {1..9}
view raw makeClosure.ts hosted with ❤ by GitHub

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!


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 = (x) => (y) => x + y,
//make a closure with scope x=5
addToFive = addTo(5),
//becomes addTo(5) => (y) => 5 + y
eight = addToFive(3)
in //becomes addTo(5) => (3) => 5 + 3
view raw closureExample.ts hosted with ❤ by GitHub

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.

(x) =>
//make selection or calculation based on parameters (x,y)
(y) => x*y
view raw fn.ts hosted with ❤ by GitHub

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.

//overwrite function type for _fn_ with a parameter list limited to x AND pass it into the scope of _fn_
fnCopy = (x) => setParameters(fn(x),{1..x})
in //wrap the previous overwrite in another function call with allowed parameters {1..9}
view raw makeClosure.ts hosted with ❤ by GitHub

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.


    • 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 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 🙂


Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s