Custom Power Query Functions for Beginners

***TIP: Subscribe to our newsletter. Don’t miss any new content.***

Frequently, you will find yourself creating a same IF function (or any other function) over and over and over and over… So why don’t we just create the function ONCE and then keep re-using this function over and over and over…? Read more to find out how to build your own custom function exactly fitting your needs.

Typical scenarios

…where you can make use of your own custom functions:

  • You frequently need to determine whether a campaign is Brand or Generic
  • You frequently need to extract Xth element from campaign name
  • You frequently need to classify keywords

Process of Creating Your Very First Custom Function

  1. Identify the custom columns which you keep adding over and over
  2. Create the custom function as a separate “query”
  3. Use the custom function in stead of the fully written function as you would normally do

Examples

You have a default table such as:

And you need to add 2 new custom columns [Fruit] and [Second Word] which tell you what kind of fruit the keyword contains and you also want to extract the 2nd word:

At the same time, you know that the formula you would use for the new [Fruit] and [Second Word] columns would be used number of times in the project (or in other future files). So you are thinking about creating custom functions “FruitFinder” and “PullSecondWord” which could be re-used over and over.

FruitFinder

You need a function which checks if keyword contain specific words. Normally, you would write something like:
if Text.Contains(Text.Lower(Text),”apple”) then “Apples” else
if Text.Contains(Text.Lower(Text),”bananas”) then “Bananas” else
if Text.Contains(Text.Lower(Text),”avocado”) then “Avocados” else
“Other”

More about IF functions here and here.

In stead of writing the above crazy formula over and over, you want to simply write this:

Notice the custom “FruitFinder” function we are about to create.

PullSecondWord

You need a function which extracts second word from the keyword. Normally, you would write:
List.First(
List.Range(
Text.Split(Text,” “)
,1,1)
)

More about List Functions here.

In stead of writing the above crazy formula over and over, you want to simply write this:

Now, it is time to convert these functions in custom functions 🙂

FruitFinder Custom Function

Let’s start with creating an absolutely blank query – New Source > Other Source > Blank Query. Open the “Advanced Editor” and enter this function (You can download the working file here):

Now, let’s have a closer look on what you see.

Highlighted Rectangles

Red items are opening/closing lines around the function which you would normally create via “Custom Column”. I always match the name  in “let FruitFinder = (Text) =>….” and in “in FruitFinder” with the name of the this query (which is in fact the name of the custom function). It’s not necessary but it helps keep the things tidy. What matters at the end is only how you call the query (custom function) = Orange rectangles. Obviously, the last items are the green ones. Those are the variables (for you “columns”) over which the custom function is going to be processed when being deployed via custom column in your main query.  In our case, it will be [Keyword]. The new custom column using the function will then be written as FruitFinder([Keyword]). Basically, you tell FruitFinder to return values based on the [Keyword] column.

Once you create your FruitFinder function, you click “Done”. You can then return to your main query with your data which is supposed to be processed:

Add custom column with your custom function:

And get the desired result:

PullSecondWord Custom Function

You just follow the similar procedure to create “PullSecondWord” function as you just did with “FruitFinder”.

So first, you need to define your custom function:

Then you need to use “PullSecondWord” function in your main query:

And then you just check your result:

Alright, so this is cool but what’s the point of creating the custom functions?

Here are few advantages:

  • You can use the function over and over within one file
  • These functions can be easily copied between spreadsheets – just right click the query name > Copy > Open a new Excel Workbook > Go to Power Query > Paste
  • If you change your custom function, the change is automatically applied to all the queries within the file (not across multiple files) in which you use the custom function. So in stead of fixing a custom column in 5 places, you can fix your custom function once.

Let me know what you think in the comments below!

***TIP: Subscribe to our newsletter. Don’t miss any new content.***

Leave a Reply

Your email address will not be published. Required fields are marked *