In a programming language such as M, with more than 630 functions, it is more than just useful if these functions are well documented. However, this also applies to custom functions in M. Even if there are already several articles on this topic, I hope that this article will help to create even more understanding.

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.

What is a documentation and why do I need one?

If you have already written your own custom functions in M, you surely know the value of a well-documented function. If not, take a look at the documentation of List.Sum():

By typing = List.Sum into the formula bar of a Power Query query you get the official documentation of this native M function. This documentation can provide the following information:

• Name of the function
• function description
• the function parameters, including their names, data types and if they are optional or not
• the syntax of the function and the data type of the returned value
• an example on how to use the function

This information about the function structure and its use makes it much easier for the user to find the right one with currently more than 630 functions. It would therefore be very useful to be able to add such a documentation to a custom function. I’ll show you how to do that.

My tiny custom M function

The following custom function is the one to be documented in this post:

This function does the following: The function takes the two parameters date1 and date2 (both of type date) and creates a list of date values, starting with date1, up to and including date2.

I would like to add documentation for this function so that other users can use it more easily. This can also be very helpful for myself if I have written a lot of own functions. To add documentation to this function, I need to modify the metadata of the function. That’s why I would like to take a brief look at the subject of metadata.

Metadata

Metadata is additional data for a specific value, stored in a so called metadata record. Metadata is a large subject area and requires its own post (which I will write in near future). You can find an introduction to this topic under point 1.7 in the Power Query Formula Language Specification. Nevertheless, I would like to briefly discuss some of the key features here.

Metadata in general

As already mentioned, metadata is additional information that can be assigned to values (records, tables, etc.). These are stored in a single record. If no metadata is stored, the metadata record is empty. The stored metadata can be accessed with the function Value.Metadata(). An example certainly makes this more vivid:

This example shows the following steps:

1. The initial situation: The base table with column Row, which contains the row numbers and column Values, which contains sample values.
2. In this step I create a new column that contains the values, but also the row number that is stored within the metadata, by using the key word meta, followed by the metadata record.
3. I delete the column Row, so that only the column Value + Metadata remains.
4. I use the formula Value.Metadata([#"Value + Metadata"])[Row]to retrieve the row number information from the metadata.

The functions metadata

You can add pretty much anything you want to the metadata of your function, but there are some pretty interesting key words, that you should know. These key words will be displayed by Power Query in the function documentation and they are the following:

• Documentation.Name
• Documentation.LongDescription
• Documentation.Description
• Documentation.Examples

Keep that in mind: If you define Documentation.Description and Documentation.LongDescription, there will only be shown the LongDescription.

There are two versions of defining and adding metadata to a custom function, that I know about. I will start with the more common one.

Defining and adding metadata to the custom function – Version 1

If you search in forums or read blogs on this topic, you will most likely come across this version. I show them for the sake of completeness, because I prefer version number 2.

Metadata definition

As I mentioned before, metadata is stored in a record, so we have to define a record.

This record has the following fields:

• Documentation.Name of type text,
• Documentation.Description of type text,
• Documentation.Example of type list. Type list is necessary, because you can save more than one example. This list is a list of records and the record’s fields are:
  • Description of type text; usually stores the same information as Documentation.Description;
  • Code of type text; here you can show how the usage of the function looks like;
  • Result of type text; Here you can show how an example return value of the function could look like.

My next task is to add this metadata to the function.

Adding metadata to the function

Now, that I have defined the metadata for my custom function, I want to add them to the function.

Therefore I do the following steps:

1. I wrap my original function in a further let-expression. Why do I do that? This gets important in step 3, where I want to overwrite the functions metadata. Therefore I need „access“ to the function itself. A characteristic feature of let-expression is that after that in all variables can be accessed, that are located between the let and the in. For detailed information about this behaviour, please read the following series of post, regarding the Environment concept in M.
2. In this step I put in the metadata, which I already defined in my metadata record Documentation_Metadata. I removed the complete definition from this screenshot, to make it less complex. You will see the complete code at the end of this post.
3. This step overwrites the metadata of my custom function fn with the newly defined metadata record Documentation_Metadata. Let us consider the function from the inside out.
   • Value.Type(fn)returns function, because the type of the custom function fn is function.
   • Value.ReplaceMetadata()replaces the input’s metadata information. The input is function and I replace the metadata of this type by my record Documentation_Metadata. The result is a type function with a new metadata record. This is important for the next step.
   • Value.ReplaceType()takes the function fn as first parameter and replaces the old type function with the new type function, which now has the metadata record inside.

Now that I have completed all the necessary steps, let’s take a look at the final result for this post.

The final result

I have taken all these steps to ensure that my custom function is adequately documented both for myself and for third parties. The following screenshot shows the result of my work and the transition from source code to help texts in Power Query.

In my next post I will show the version 2, my preferred variant to add a documentation to my custom functions.

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]