Please note javascript is required for full website functionality.


Power Query: Customising Custom Built Functions

27 December 2017

Welcome to our Power Query blog. This week I look at how to customise function descriptions by using M function meta-data.

In Power Query: Custom Built Functions, I created my own function called FormatExpenseType which combined a text and a number field to give me my expense type:

In my workbook, the function is shown in the query pane:

I would like to provide a potential user of my function with more information to help them use it. In M language, there are a collection of functions under the heading ‘Documentation’. Since this is a part of how M language is constructed, these functions are not easy to find. They are all in the Power Query M Language specification, which can be found here, which is a good read if you are having trouble sleeping. These are the functions that I am interested in:

Documentation.Examples          list     

List of record objects with example usage of the function.  Only displayed as part of the function information.  Each record should contain the following optional text fields: DescriptionCodeResult

Documentation.LongDescription    text

Full description of what the function does, displayed in the function information

Documentation.Name        text  

Text to display across the top of the function invocation dialog

Documentation.AllowedValues      list

List of valid values for this parameter.  Providing this field will change the input from a textbox to a drop-down list.  Note: this does not prevent a user from manually editing the query to supply alternate values

Documentation.FieldCaption text  

Friendly display name to use for the parameter

Documentation.FieldDescription   text

Description to show next to the display name

Documentation.SampleValues list  

List of sample values to be displayed (as faded text) inside of the text box.

I use some of these functions in a blank query, as shown below so that it is clear which functions are controlling the descriptions and examples.

When I click ‘OK’ (my function is displayed in the way I have determined) note the deliberate mistake: I have called my function a number instead of text (func() as number).

This shows how the descriptions and examples are definitely controlled by me – even down to calling it a numerical function when it isn’t! I can control the metadata in any custom function that I create. Come back next time for more ways to use Power Query!

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.