Power BI - How to document a custom Power Query function

Colin Maitland, 17 April 2022

In a previous article, I demonstrated the configuration of a Custom Power BI UTC to Local Date/Time with Daylight Saving Time Function named UTCDateTimeZoneToLocalDateTime. Power Query can use this custom M function in a Power BI data model connected to Dataverse to transform UTC Date/Time/Zone data type values to New Zealand Local Time (NTLT) Date/Time data type values taking into account New Zealand Daylight Savings.

In this article, I will demonstrate how the function can be documented for the users of the function.

Example 1 - Without Function Name, Description and Examples Documentation

Here is an example of this function with only the UTC Date/Time/Zone parameter documented.

Here is a redacted view of the configuration of the original function. Notice that the documentation for the UTCDateTimeZone parameter is included as part of the configuration for that parameter at lines 9 to 13.

Example 2 - With Function Name, Description and Examples Documentation

Here is an example of this function with the function documented in addition to its parameters. You will notice that the documentation now includes the name, description and usage examples for the function.

In the rest of this article, I will now demonstrate two patterns that may be used for adding documentation about the function as part of its configuration.

Configuration Pattern 1 - Using type function( ... )

In this section, I provide an example of the first of two patterns that may be used for adding documentation to a function.

Here is a redacted view of the updated function. The updated function is the same as the original function except for the fact that lines 1 to 17 of the original function are replaced with lines 1 to 39 of the updated function. You will notice that the updated function uses (a) ‘FunctionType = type function’ instead of ‘FunctionType = Type.ForFunction’, (b) a modified configuration for the Parameters and Return Type, and (c) documentation for the function from lines 17 to 39.

The metadata for the function includes several standard documentation fields, i.e. Documentation.Name, Documentation.Description and Documentation.Examples. Each of the examples has several standard documentation fields, i.e. Description, Code and Result. You can refer to this article for more information on these and other Documentation fields.

Simplification

As an aside, both the original and the updated function can be simplified by replacing the five lines from 100 to 104 of the original function with these two lines:

Configuration Pattern 2 - Using Documentation_Metadata

In this section, I provide an example of the second of two patterns that may be used for adding documentation to a function.

When using Configuration Pattern 1, there is a side-effect of not using the Type.ForFunction method as shown in lines 1 to 17 of the original function. There is no longer a configuration for the minimum number of parameters required to invoke the function. However, the use of “type function” does allow you to use the ‘optional’ keyword to configure which parameters are optional versus those that are not.

Alternately, you can retain the use of the Type.ForFunction method and instead alter the last lines of the function as shown here. In this version, lines 1 to 17 of the original function have been retained without any change, but lines 100 to 104 have been replaced with these lines. The key step here is at line 128. The use of Value.ReplaceMetadata here replaces the metadata configured in the Type.ForFunction method with that configured on lines 101 to 123.

Conclusion

In conclusion, the result of the using the two patterns demonstrated in this article, is that informative documentation about the function is displayed to the user when they select the function in Power Query. This documentation informs the user of what the function does and provides examples of how to use it. The approach that you use depends on your personal choice and style. My preferred approach is Configuration Approach 1.