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: Description, Code, Result
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.
