If you’re interested in cascading parameters and Power BI custom connectors is an option for your project, you should give it 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 following few scrips tangled between functional programming concepts. You will also learn about:
- Where to set the allowed values for function parameters
- How to build function inception with higher-order functions
- How to access a variable outside its scope
- And finally, get to a result that mimics cascading selections
Documenting the parameters
Weirdly enough, there isn’t much documentation on how to document your functions. Except for Chris’ and Imke’s blogs, this information is scattered across various forums and Q&A sites. [*]
For 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)=> | |
| let | |
| //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 | |
| Value.ReplaceType(f,myFunType) |
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.
| let | |
| ring = () => () =>[Answer="Hello"], | |
| firstCall = ring(), //returns a function | |
| secondCall = ring()() //returns Answer="Hello" | |
| in | |
| secondCall |
Let’s start with a function fn, which returns the value of the parameter x.
| (x) => x | |
| //do filtered operations on 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.
| let | |
| fnCopy = setParameters(fn,{1..9}) | |
| in fnCopy |
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 a copy of the original function fn again but with a filtered down list for the allowed values of x.
| let | |
| //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} | |
| setParameters(fnCopy,{1..9}) |
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 essential 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 outer function’s variables. 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. Let us follow an example:
| let | |
| 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 | |
| eight |
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 the stored environment [x=5].
To get back to our original example, 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 |
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 passing it as part of the calling environment.
| let | |
| //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} | |
| setParameters(fnCopy,{1..9}) |
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
Query Power 
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.
LikeLike
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 🙂
LikeLike
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.
LikeLiked by 1 person
Is Navigation Tables an option at all?
LikeLike