The other day when I was thinking about Power Query functions (as you do) it occurred to me that they would be a lot more usable if, when you invoked them, you could show your users a dropdown box for each parameter where they could choose all of the allowed parameter values rather than expect them to type a value which may or may not be correct. I thought this was such a good idea that I sent an email to the Power Query team, and within a few hours I got a reply from Matt Masson… saying that they had already implemented this some time ago but hadn’t documented it yet. He also, very kindly sent me a code example, and allowed me to blog about it (thanks Matt!). In this post I’m going to look at a very simple example of how to do this; in part 2 I’ll show some a complex scenario involving SQL Server.
Consider the following Power Query function that multiplies two numbers:
[sourcecode language=”text” padlinenumbers=”true”]
(FirstNumber as number, SecondNumber as number) as number =>
FirstNumber * SecondNumber
[/sourcecode]
Enter this into a new blank query and you’ll see the following in the Query Editor:
Clicking either the Invoke Function or Invoke button will make the Enter Parameters dialog appear:
You can then enter two numbers, click OK, and the query will invoke the function with those two numbers and show the result:
Now it turns out that there are three properties you can set on a function parameter to control how it is displayed in the Enter Parameters dialog:
- A text description of the parameter
- A sample value
- A list of allowed values that can be passed to the parameter, that can be selected from a dropdown box
Unfortunately the way to set these properties isn’t exactly straightforward, but the basic process is this:
- For each parameter create a custom type, and then use a metadata record to assign the property values for that type
- Then create a custom function type, where each parameter uses the custom types created in the previous bullet
- Finally take your existing function and cast it to your new function type using the Value.ReplaceType() function.
OK, I know, this sounds scary – and it is, a bit. Here’s how the original function can be rewritten:
[sourcecode language=”text”]
let
//declare a function
MultiplyTwoNumbersBasic =
(FirstNumber as number, SecondNumber as number)
as number =>
FirstNumber * SecondNumber,
//declare custom number types with metadata for parameters
MyFirstNumberParamType = type number
meta
[Documentation.Description = "Please enter any number",
Documentation.SampleValues = {8}],
MySecondNumberParamType = type number
meta
[Documentation.Description = "Please choose a number",
Documentation.AllowedValues = {1..5}],
//declare custom function type using custom number types
MyFunctionType = type function(
FirstNumber as MyFirstNumberParamType,
SecondNumber as MySecondNumberParamType)
as number,
//cast original function to be of new custom function type
MultiplyTwoNumbersV2 = Value.ReplaceType(
MultiplyTwoNumbersBasic,
MyFunctionType)
in
MultiplyTwoNumbersV2
[/sourcecode]
Now, when you invoke the function, the Enter Parameters dialog looks like this:
Note how for the FirstNumber parameter the description and the sample value is displayed; and how for the SecondNumber parameter the dropdown list shows all of the allowed values in the list we declared (remember that {1..5} returns the list {1,2,3,4,5} in M) and how it is no longer possible to type a value here.
Three things to mention last of all:
- Although Documentation.SampleValues is a list, only the first value in the list seems to be displayed
- Documentation.AllowedValues doesn’t actually prevent you from calling the function with a value not in this list, it just controls what values are seen in the dropdown list
- The Workbook Queries pane no longer recognises your function as a function when you do all this – it doesn’t have the special function icon, and there is no Invoke option on the right-click menu. This is a real shame because, while it doesn’t affect how the function works, it does make it much less intuitive to use and the whole point of this exercise is usability. Hopefully this gets fixed in a future build.
You can download the a sample workbook containing all the code in this post here.
Good tip
Hi Chris, thanks for the function. I did a follow up post http://tinyurl.com/l3bd3g3 to convert static list {1..5} to a dynamic list.
Thanks George – that’s more or less what I’m going to show in part 2, only there I’m getting the values to populate the list from SQL Server 🙂
Chris,
Any idea if you can also implement a collapsible input box similar to the “SQL statement” expander on the SQL Database prompt? It is very interesting to see that there is functionality like you’ve shown available despite the lack of documentation for it at this point, so I wouldn’t be surprised if something like this might be possible.
Thanks.
No, I’m pretty sure you can’t do that, sorry.
Chris, any ideas how to implement Allowed Values checking for some columns?
For instance, I want to have a checking column where I could see what row contains value in a specific column not from Allowed list.
Do some of the examples in this post help here? https://cwebbbi.wordpress.com/2014/01/27/comparing-columns-in-power-query/
It’s a cool stuff but not exactly what I need. A have a huge table with o lot of columns. And for some of them I have a lists of Allowed values. I need to check all rows of those specific columns and see where they don’t match my allowed values. I’m thinking about writing a few functions with lists of allowed values but not sure yet how to do it.
The question’s post is here:
http://www.mrexcel.com/forum/power-bi/823305-power-query-allowed-values.html
Chris,
I would ask if it’s possible to force a query in Excel, that calls a function with parameters, to ask for new parameters each time I update it.
I mean that, when I call the query for the first time, Power Query creates the code to call the function, and the parameters are set “statically”.
What I would like to have is the possibility to call again this query and, instead of simply run it again, to be able to set new parameters “dynamically”. In other words: it’s possible to show the parameter window every time?
I don’t know if my explanation is clear enough (I’m sorry for my English).
Thank you for your help.
Chris,
I would ask if it’s possible to force a query in Excel, that calls a function with parameters, to ask for new parameters each time I update it.
I mean that, when I call the query for the first time, Power Query creates the code to call the function, and the parameters are set “statically”.
What I would like to have is the possibility to call again this query and, instead of simply run it again, to be able to set new parameters “dynamically”. In other words: it’s possible to show the parameter window every time?
I don’t know if my explanation is clear enough (I’m sorry for my English).
Thank you for your help.
I understand what you want, and it’s something I’ve tried to do lots of times too. Unfortunately I’m 99% sure it’s not possible. What you need to do instead is to write your query so that it reads the parameter values from cells in the Excel worksheet before it goes to the main data source. The user can then enter whatever parameter values they want in Excel, then refresh the query. You can see lots of examples of this in the presentation referenced here: http://blog.crossjoin.co.uk/2015/04/17/building-a-reporting-solution-using-power-query/
Hey Chris, do you think it is possible to implement this dropdown list of values that feed a function inside a Navigation Table?
I currently allow the user to type the values, but if I could control the values entered with a dropdown it would be much better. So far I couldn’t do it.