How to create and use custom functions in Power Query to retrieve data

This tutorial will show you how to create and use custom functions in Power Query for Power BI and Excel to retrieve data.

Power Query Functions allow you to create an algorithm to perform a specific unit of work. The most common functions are used to query specific endpoints multiple times using parameters.

In order to do make the most use of this technique, you will need to understand both how to create and apply parameters, as well as how to turn an endpoint into a function.

At the end of this tutorial, you will have learned how to:

You can see it in explained and in action in the following video. You can also download the accompanying Power BI file used in the video.

Creating Parameters in Power Query

Before you can create a function, you will need to create parameters.

Parameters allow you to create a variable that represents a specific value. This parameter can then be applied to an endpoint to refine a specific step.

Parameters have the added benefit that, when coupled with functions, allow you to pass values directly to your endpoint.

To create a parameter, follow these steps:

  1. Using the Home ribbon, click Manage Parameters then New Parameter.
  2. Enter a name, select the data type and enter a value for the parameter.
  3. Click ok to add the parameter.

Back to top

Applying Parameters in Power Query

Once parameters are created, applying them simply requires you to add or update existing steps (such as filters) and use the parameter defined.

To apply a parameter to an endpoint, follow these steps:

  1. Select the filter step.
  2. Click on the Gear Icon.
  3. Change the dropdown parameter to Column Name.
  4. Select the parameter.
  5. Click ok to save the changes.

Back to top

Creating functions in Power Query

Creating a function from an existing endpoint is a simple process.

  1. Right-click on the endpoint name and choose Create Function.

Back to top

Calling functions in Power Query

Once the function exists, it can be added as a custom column in another endpoint or data set.

To call a function using a custom column, follow these steps:

  1. Select the data set for which to call the function.
  2. On the Add Column ribbon, click on Invoke Custom Function.
  3. Enter a name for the new column.
  4. Select the function from the dropdown.
  5. Enter the parameters.
  6. Click ok to add the custom column.
  7. Click on the dropdown above the column name in the data view
  8. Expand and tick the columns to include in the data view.

Back to top