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:
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:
Then, I need to access the Advanced Editor:
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)
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.
Choosing to invoke creates a new query rather than adding an unnecessary step to my function:
My query is now shown as a function in my workbook, and hovering over it shows me the details behind it.
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.
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’:
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.
This time, instead of values, I choose columns as my parameters:
I select the expense code and Expense Category columns.
I have deliberately used a query where I already have my original Expense Type column for comparison, so now I test my function:
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.
Come back next time for more ways to use Power Query!