Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query–Part 1

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:

(FirstNumber as number, SecondNumber as number) as number =>
FirstNumber * SecondNumber

 

Enter this into a new blank query and you’ll see the following in the Query Editor:

image

Clicking either the Invoke Function or Invoke button will make the Enter Parameters dialog appear:

image

You can then enter two numbers, click OK, and the query will invoke the function with those two numbers and show the result:

image

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:

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 

 

Now, when you invoke the function, the Enter Parameters dialog looks like this:

image

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.

14 thoughts on “Specifying Allowed Values, Sample Values and Descriptions For Function Parameters In Power Query–Part 1

  1. 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.

  2. 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.

  3. 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.

  4. 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.

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