How to Create a Custom Function in Power Query

Excel’s Power Query is a powerful data transformation and analysis tool that allows users to retrieve, clean, and shape data from various sources. While Power Query provides an extensive set of built-in functions, there may be scenarios where you need to perform custom operations on your data. This is where custom functions in Power Query come into play. In this article, I will go over how to create a custom function in Power Query that you can invoke and re-use.

Steps to creating a custom function in Power Query

Creating custom functions in Power Query involves using the M language, which is the scripting language underlying Power Query. It can be complicated to create but I’ll show you two ways you can create a function. The first method is directly through coding, the other is after converting a query into a function.

In this example, I’m going to pull all the stocks that are contained from a list of exchange-traded funds (ETFs). I’ve created the following table for this purpose, called tblETF:

A table of different exchange-traded funds listed.

Creating a custom function from scratch

If you’re creating a function in Power Query directly from code, here’s how to do that:

1. Go to load the data into Power Query by selecting a cell in your table, then click on the Data tab and click From Table/Range.

Launching Power Query from the From Table/Range button.

2. That will open up Power Query. Once there, on the Home Tab, click on the Advanced Editor button:

Selecting the Advanced Editor option in Power Query.

3. Create a name for the function using the let variable. In this example, I’m going to call it getholdings and it will pull all the holdings from the etf field. The opening line of the code is as follows:

let getholdings = (etf) =>

4. Next, list the commands that the function should execute. I’m going to pull the data from the page relating to the etf. This requires using the Web.Contents function and modifying the URL so that it includes the etf symbol:


Source = Web.Page(Web.Contents(""&etf&"/holdings/")),

Then, reference the data as follows:

Data0 = Source{0}[Data],

The full code looks like this:

Custom macro in Power Query that gets the ETF holdings.

5. Now that the function is created, go into the query for the list of ETFs. Create an additional column from the Add Column tab, and select the button to Invoke Custom Function.

Selecting the Invoke Custom Function option in Power Query.

6. Set a column name for the new column. Then, specify the function query to reference. And you’ll also need to specify where the ETF value is coming from, which involves selecting the column:

Invoking a custom function in Power Query.

7. Next, you’ll expand the table that has been created within the column. This is done by pressing on the icon that shows arrows going in opposite directions. Then, select all the available columns.

Expanding a table in Power Query.

You should end up with something that looks like this:

Fully expanded Power Query table showing holdings by ETF.

You can now click Close & Load and this data will load in your Excel spreadsheet. Now you can add to your ETF list and refresh the data, and the table of all the holdings will populate.

List of ETFs and their related holdings in two separate excel tables.

Converting a query into a function

If you’re not comfortable coding with Power Query, you can first create the steps, and then convert the query to a function.

First, it’s necessary to create the query. In the previous example, I loaded the URL from a dynamic web page. To do that, I’ll start with selecting the From Web button on the Get & Transform Data section:

Using the From Web button on the Get & Transform Data section in Excel.

Next, populate the entire link, without the ETF variable — this will be added later:

Setting the URL that you want to pull data from into Power Query.

Then, select the table that contains the data and click the Load To button and select connection only:

Selecting the table in Power Query that contains the ETF holdings.

Then, right-click on the query to edit it so that you’re back in Power Query. From there, click on the Advanced Editor and you should see this:

Power Query advanced editor showing the code to extract data from a web page.

This is similar to the code in the first approach. To convert this into a function, we need to add another let variable and specify the function name, and any variables that will be used in the function. For the first line, I’ll add the following

let getholdings = (etf) =>

and for the URL, I’ll put the etf variable into there:

Source = Web.Page(Web.Contents(""&etf&"/holdings/")),

For the last line, I’ll add another in variable:

in getholdings

Here’s the updated code, with the changes highlighted in yellow:

Now I’ve converted my query into a function that can be invoked.

If you liked this post on How to Create a Custom Function in Power Query, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.

Comments are closed.