The good documentation of custom M functions is a key factor for efficiency in Power Query and Power BI Desktop. In my first post on this topic, I explained why documentation is important and how to add those to custom functions by changing metadata. If you haven’t read the previous article, you should do so before proceeding with this one here. In this post I show my favorite variant to add documentation to my custom functions. 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 read it here.

Defining and adding metadata to the custom function – Version 2

In my previous post I defined a record, which then functioned as my metadata record. Then I changed the type of my custom function and in this step I added the new metadata record to my custom function. In this Version 2, which I prefer over Version 1,  I will explicitly define a new type function with its associated metadata record. This gives me greater flexibility to access the individual parameters of the function. This will be part of the third part of this series in a couple of weeks.

Metadata definition

This time, the definition of the metadata record doesn’t look different to Version 1, but I do not only define a metadata record, but a complete new type value. Take a look at the following screenshot:

What you can see is:

1. The definition of a new type value fnType of type function. This type value contains all the parameters of my custom function, with their specific data types (date) and the data type of the function’s return value (list). Behind the definition of the type value you see the key word meta, which introduces the definition of the metadata record
2. This is the definition of the metadata record, as you already know it from my previous post.

You might think: „And why is this his prefered version of adding documentation to a custom function? This looks much more difficult!“ This may be true, but this version allows me to modify the metadata not only for the custom function, but also for each parameter of the custom function. How this works and what it can be useful for, you will find out in the next post 🙂 If you want to dig deeper into the M type value system, take a look into the Microsoft Power Query for Excel Formula Language Specification, chapter 5 (Types). Now let’s add this type to my custom functon.

Adding metadata to the function

Adding this type (including the documentation) to my custom function is pretty easy:

At the end of the script I simply replace the type of my function fn by the newly created type fnType, using the function Value.ReplaceType(). The result is the same, compared to my previous post:

My next post will cover how you can document single parameters and also define allowed values. At this point using version 2 brings real added value.

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]