Using the Power Query Add-In - Microsoft Business Intelligence Tools for Excel Analysts - page 142

Creating and using a basic custom function
When building a custom function for Power Query, you're creating a query and manipulating its
M code to return a desired result. That result can be an array, a data table, or a single value.

In this section, you build a basic mathematical function that calculates profit. This function takes a
revenue amount and a cost amount and outputs a profit amount using a basic mathematical
operation:

Revenue – Cost = Profit

For basic functions such as this one, you can start with a blank query and enter the needed M code
from scratch:

1. From the Power Query tab, choose From Other Data Sources ➜ Blank Query.

The Query Editor window opens.

2. In the Query Editor Ribbon, click the View tab and click the Advanced Editor button.

3. In the Advanced Editor window, replace the starter syntax with the following code in the
code box:

let Profit = (Revenue, Cost)=>
Revenue-Cost
in Profit

The first line tells Power Query that this function needs two parameters: Revenue and Cost. The
second line tells Power Query to subtract the Cost parameter from the Revenue parameter. The
last line of the code tells Power Query to return the result.

Power Query doesn't care what you name the functions as long as the names start with
a letter and don't include any spaces.

Tip

Figure 7-17 shows what the code looks like in the Advanced Editor window.

Figure 7-17: Enter your custom code in the Advanced Editor window.