In my two previous posts you could see how to add documentation to custom M functions in Power Query for Excel and Power BI Desktop. In the current post I describe how such documentation can be added to the individual parameters of a function. I will also discuss how to make allowed values of a parameter selectable by dropdowns. Have fun.

You can download all code samples from here. If you already have my Power Query editor for Notepad++ (or want to build it) you can open the file in Notepad++. Otherwise open it in a simple text editor and paste it into the Advanced Editor of Power Query or Power BI Desktop. I will refer more frequently to the Microsoft Power Query for Excel Formula Language Specification. You can download it here.

Extend the functionality – sort the list in ascending or descending order

Now I would like to extend the funtionality of my custom function in that way, that the user can choose, wether the returned list of dates will be sorted in an ascending or descending order. Therefore I do the following steps:

• I add the optional parameter SO (short for sort order).
• I also change the functions‘ code, so that the list will be sorted in ascending order, if SO is null, or „ascending“. If it is something different, the sort order will be descending.
• I add SO also to the definition of my custom type fnType

All these changes are highligted in red in the following screenshot:

The result is the following:

With this additional parameter I am now able to sort the date list in ascending or descending order by typing the words „descending“ or „ascending“ into the text box. A much better user experience would of course be if this were not a free field, but sorting could be selected via a dropdown. Let’s do this in the next step.

Limiting parameters to Allowed Values

Passing values of parameters as free text can often lead to errors. It would be better to use a dropdown, which lets you select the allowed contents of the parameters. This can be achieved via Documentation.AllowedValues as part of the metadata record of the specific parameter.

What has a been a usual textbox before, now is a dropdown, with only the allowed values (and an empty value) to choose for the user.

These allowed values are static. They won’t change over time. But what if I need the allowed values to be dynamical?

Dynamically control Allowed Values of the parameters

Now let’s say I want to dynamically set the values to choose. What if I want the user to be able to choose only date values from the future?

What I do in the above script is the following:

• I define the variable Tomorrow as todays date (DateTime.Date(DateTime.LocalNow())) plus 1 day ( + #duration(1,0,0,0))
• In addition I define the allowed values for both, date1 and date2 as a dynamic list of 100 dates, starting from tomorrow, by using List.Dates(Tomorrow, 100, #duration(1,0,0,0))

The resulting documentation looks like this:

As you can see in the screenshot above, the parameter date1 (and also date2, even it is not visible on the screenshot) got a different user interface. While I had a small calendar icon first, to choose a specific date, I now have a dropdown list with only the allowed values in it. See the difference here:

Adding more valuable information to the function parameters

In addition to Documentation.AllowedValues, there is some other information that can be added to the documentation of a custom functions parameters. Those are the following:

• Documentation.FieldCaption:
• Documentation.FieldDescription
• Documentation.SampleValues

The following script uses the above mentioned fields within the metadata record:

The expectation is that these added fields in the metadata record will lead to more information in the documentation. However, a look at the documentation shows that this is unfortunately not the case:

Hmm, that wasn’t what I expected. I have only found one way to make the parameter documentation visible anyway: By removing the documentation of the function (not the parameter), which is the red marked part in the following screenshot:

By removing all the red marked part of the M script, the documentation for the parameters becomes visible (but of course the documentation of the function disappears):

For me it looks as if I always have to choose between the documentation of my function or the documentation of my function parameters. Both at the same time seems to be impossible, which of course makes little sense. But just because I couldn’t do it doesn’t mean that it can’t be done. If anyone out there has an idea of how it works, please say 🙂

My thanks go to the authors of the following excellent contributions on this topic: Chris Webb, Matt Masson, Imke Feldmann:

• https://github.com/Microsoft/DataConnectors/blob/master/docs/function-docs.md
• https://blog.crossjoin.co.uk/2014/11/27/specifying-allowed-values-sample-values-and-descriptions-for-function-parameters-in-power-querypart-1
• https://blog.crossjoin.co.uk/2014/12/02/specifying-allowed-values-sample-values-and-descriptions-for-function-parameters-in-power-querypart-2
• http://www.thebiccountant.com/2017/05/11/how-to-edit-m-function-metadata/

Regards from Germany,

Lars

I write my posts for you, the reader. Please take a minute to help me write my posts as well as possible. Thank you 🙂

[yasr_visitor_multiset setid=2]