Using Power Query in Office 365: M Language and Data Transformations

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.

Figure 1: Creating a Calculated Column in M

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).

Figure 2: Generating a Query from an Excel Table

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).

Figure 3: Custom Column Setup for LeftOver Column

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).

Figure 4: Language in a Power Query Custom Code

Click OK and you will get the expected result (see Figure 5).

Figure 5: Column Created

Click the Home ribbon and the Apply & Close menu in order to apply the code to the worksheet (see Figure 6).

Figure 6: New Custom Column LeftOver

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).

Figure 7: Creating a Power Query Custom Function

Figure 8: Power Query Custom Function

In View Advanced Editor (see Figure 9), type the following code:

Figure 9: Creating a Power Query Custom Function

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).

Figure 10: Creating a Column Based on a Custom Function

Figure 11: Custom Column

Save the query modifications (File menu-Apply & Close) (see Figure 12).

Figure 12: Applying Query to Worksheet

It is important to keep in mind, that just like queries, functions can be shared (the data catalog) across the company.

<.25in>

’

Figure 13: On-Premises Log Files in a Folder, Ready to be Parsed

Figure 14: Log Files Current Format

Figure 15: Desired Goal

’

Figure 16: Analyzing Files Stored in a Folder

Figure 17: Power Query Returning Files Information

Figure 18: Power Query Editor: Expanding a Record Column (Attributes)

’

Figure 19: Additional Field

’

Figure 20: Power Query Editor: Filtering On File Extensions

’’

’’

File 21: Power Query Editor: Applying Instructions to Groups of Files

Figure 22: Power Query Editor: Opening Files in .Text Mode

Figure 23:Power Query Editor: File in Text Mode

Figure 24: Power Query editor: Removing Top Rows

’&

Figure 25:Wrapping the First 23 Characters in a Column

Figure 26:Dedicated Column

Figure 27: Power Query Applying Correct Type

’

Figure 28: Filtering Rows to Focus On

“”

Figure 29: Select Rows with "Error" String

Figure 30: Only "Error" Lines Filtered

Figure 31: Split rows with Comma Delimiter

Figure 32: Results

’

Figure 33: Final Result

’

Figure 34

<.25in>

<.25in>

“”

’

“”