Let’s get a little bit deeper in Power Query by looking at the M language. If you’ve not seen our first article on creating queries in Power Query, you might want to check it out.
Creating Basic M Language Code and Functions
As we mentioned before, the recommended practice is to let Power Query generate the code. In some situations however, it might be interesting to add your own custom touch.
For instance, let’s start from the following basic Excel table where we have three columns (see Figure 1): ItemID (of food), QtyServed, QtyConsumed.
To keep it simple, we are starting from an Excel table, but what we are going to illustrate applies to virtually any data source supported by Power Query.
We would like Power Query to create an additional column, LeftOver. LeftOver will be the difference between QtyServed and QtyConsumed, but we need the end result in percentage.
Right-click any cell of the table, go to the Power Query ribbon, and select from Table. Accept the default values. Power Query generates a query with the following code in the editor (see Figure 2).
Next, right-click any cell and select Insert Custom column in the Add Colum menu. This sets it up so we can create the LeftOver column (see Figure 3).
Now we will create the LeftOver custom column. It must be a percentage; the M language library provides a function Number.ToTest() where the second parameter is the formatting code. We will choose “p.” More information on this function here at the Microsoft Office site.
Right-click any cell and select Create Custom Column. Name it LeftOver. Fill in the following code (see Figure 4).
Click OK and you will get the expected result (see Figure 5).
Click the Home ribbon and the Apply & Close menu in order to apply the code to the worksheet (see Figure 6).
We can make our code reusable by creating a custom function called LeftoverFunc that will take two parameters (QtyServed and QtyConsumed) and return the difference in percentage.
Go to the Power Query ribbon, select the From Other Sources button and the Blank Query menu item (see Figure 7).
In View Advanced Editor (see Figure 9), type the following code:
Click done. We now have to add a new LeftOver Column to the initial query and to apply the function to each row.
Edit the query, add a new custom column (in the query editor, select Column-Insert Custom Column), (Figure 10) name it LeftOver2 (Figure 11).
Save the query modifications (File menu-Apply & Close) (see Figure 12).
It is important to keep in mind, that just like queries, functions can be shared (the data catalog) across the company.