Power Query: Custom Built Functions
13 September 2017
Welcome to our Power Query blog. Today I create a simple function and show how to access it when building a query.
Last week, I looked at how to access and test the built-in Power Query functions. There are occasions, however, when I am repeatedly using the same logic, and I want to store it in a custom function of my own. Let’s take a look.
Back in Uniting Different Types, I used some M code to combine two of my columns in the screenshot below:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image1.png/7322933c44b80018a5b14a4901a5bd1d.jpg)
If I often need to combine my columns in this way I can take my M code
= [expense code] & " " & Text.From([Expense Category])
and put it into a function of my own.
I begin by creating a blank query from my Excel worksheet:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image2.png/98121b6916b567395cbe022797167c49.jpg)
Then, I need to access the Advanced Editor:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image3.png/f6987cef1d7c3c6512f79cdca613a2ea.jpg)
At the beginning of my function, I need to give it a name – I choose ‘FormatExpenseType’. I need to define what parameters my function accepts (I’ve called them ‘InText’ and ‘InNumber’). Next, I apply my logic to combine the parameters and finish my function syntax by defining the output (‘result’). I can specify a value for my function should I wish (I have chosen not to, but I still need the final line):
let FormatExpenseType =(InText as text, InNumber as number)=>
let result = InText & " " & Text.From(InNumber)
In result
In FormatExpenseType
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image4.png/34a3fcfd8f7659bcf40f6053d54e6a88.jpg)
Once I have created my query, I can name it ‘FormatExpenseType’, ready to use in other queries. I can test it first by clicking ‘Invoke’, or choose to ‘Close and Load’ from the ‘Home’ or ‘File’ tabs to store the function in my workbook.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image5.png/49f45f83b3ba391b27eda5079d66b262.jpg)
Choosing to invoke creates a new query rather than adding an unnecessary step to my function:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image6.png/3313bfc6e86ccd7be32718d76faf85b5.jpg)
My query is now shown as a function in my workbook, and hovering over it shows me the details behind it.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image7.png/ca96533f6ec1123979465beb8f745287.jpg)
I can also choose to invoke from here, which would create a new query in exactly the same way as invoking from the Query Editor.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image8.png/c8943e71901c0cf0569dbbe394b8f36f.jpg)
I am ready to call this function from my ‘Table1’ query. First, I double click on ‘Table1’. In the ‘Add Column’ tab in the Query Editor, I can ‘Invoke Custom Function’:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image9.png/36ffd6daeff25d6597e95abb4709b585.jpg)
I am prompted to enter a new column name and select a function – in this case I can only see the one I have created.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image10.png/e02ad2e9f0cb300c0ed4883a9a347195.jpg)
This time, instead of values, I choose columns as my parameters:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image11.png/4a20882ccccaa9c426a4bd8837ae359d.jpg)
I select the expense code and Expense Category columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image12.png/3476d08e53a1cd80ead181ab322ce808.jpg)
I have deliberately used a query where I already have my original Expense Type column for comparison, so now I test my function:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/sep/pq-41-image13.png/fc983f293a86a368b4b161cc6156b86b.jpg)
The function works perfectly – and because it will work on any text and number column combination I could rename it to be more generic (e.g. FormatTextPlusNumber) and use it for other columns.
Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!