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:
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.
2. That will open up Power Query. Once there, on the Home Tab, click on the Advanced Editor button:
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 stockanalysis.com page relating to the etf. This requires using the Web.Contents function and modifying the URL so that it includes the etf symbol:
let
Source = Web.Page(Web.Contents("https://stockanalysis.com/etf/"&etf&"/holdings/")),Then, reference the data as follows:
Data0 = Source{0}[Data],
The full code looks like this:
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.
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:
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.
You should end up with something that looks like this:
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.
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:
Next, populate the entire link, without the ETF variable — this will be added later:
Then, select the table that contains the data and click the Load To button and select connection only:
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:
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("https://stockanalysis.com/etf/"&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.