What Is the M Language?

I should, nonetheless, begin with a few technical stakes in the ground to explain what the Power Query Formula Language (or M as everyone calls the language now) is and what it can—and cannot—do.

M is a functional language. It is certainly not designed to perform general-purpose programming. Indeed, battle-hardened programmers will search in vain for coding structures and techniques that are core to other languages.

I don’t want to get too technical at this juncture. Nonetheless, I hope that a high-level overview will prepare you for some of the approaches that you will learn later in this chapter.

M and the Power Query Editor

The good news about M is that you can already write it. By this I mean that every example that you followed in the previous 11 chapters wrote one or more lines of M code for you. Indeed, each step in a data load and transformation process that you generated when using the Power Query Editor created M code for you—automatically.

Modifying the Code for a Step

If you feel that you want to delve into the inner reaches of Power Query, you can modify steps in a query by editing the code that is created automatically every time that you add or modify a query step.

To get a quick idea of what can be done:

1. 1.

   Open a new Excel file.

    
2. 2.

   In the Data ribbon, select Get Data ➤ From File ➤ From Worksheet.

    
3. 3.

   Select the Excel file C:DataMashupWithExcelSamplesBrilliantBritishCars.xlsx.

    
4. 4.

   Select the source table named BaseData.

    
5. 5.

   Click Transform Data to open the Query Editor.

    
6. 6.

   Select the column IsDealer and remove it.

    
7. 7.

   Click the Remove Columns step in the BaseData query. You will see the “M” code in the formula bar. It will look like that shown in Figure 12-1.

    

1. 8.

   In the formula bar, edit the M code to replace IsDealer with ReportingYear.

    
2. 9.

   Press Enter or click the tick icon (check mark) in the formula bar to confirm your changes.

    

The step and subsequent data will be updated to reflect your changes.

The modification that you carried out in step 8 effectively means that you are adding back the IsDealer column and removing the ReportingYear column instead. You could have done this using the interface (by clicking the gear cog icon in the Applied Steps list for this step), but the whole point is to understand that both options are available and that the Power Query interface is only generating and modifying M code. So you can modify this code directly, if you prefer. Indeed, modifying M code is often faster than making a series of interface-based maneuvers.

If you are an Excel power user (as many Power Query aficionados are), then you can be forgiven for thinking that this is similar to Excel Macro development. Indeed, it is in some respects:

• The core code can be recorded (VBA for Excel, M for Power Query).

• The resulting code can then be modified.

This is, of course, an overtly simplistic comparison. The two approaches may be similar, but the two languages are vastly different. Yet if this helps as a metaphor to encourage you to move to M development, then so be it.

There are, inevitably, a series of caveats when modifying the M code for a query step in the formula bar. These include (but are far from restricted to)

• Any error will not only cause the step to fail, it will cause the whole data load and transformation process to fail from the current step onward.

• You need to remember that M is case-sensitive—and even the slightest error of capitalization can cause the entire process to fail.

• The use of quotes to define literal elements (such as column names) must be respected.

• M makes lavish use of both parentheses and braces. It can take some practice and understanding of the underlying logic to appreciate their use fully in various contexts.

Fortunately, M will provide fairly clear error messages if (or when) errors creep in. If you enter an erroneous field name, for instance, you could see a message like the one in Figure 12-2.

I do not want you to feel that modifying M code is difficult or dangerous, however. So, to extend the example given earlier, this is what the M code would look like if you extended it to remove two columns, and not just one:

= Table.RemoveColumns(#"Changed Type",{"ReportingYear", "IsDealer"})

Note

More generally, it is often best to look at the code for existing steps—or create “dummy” code using a sample dataset in parallel—to get an idea of what the M code for a particular function looks like. This will then indicate how best to modify the code.

M Expressions

To give you a clearer understanding of what each M “step” contains, Figure 12-3 shows the core structure of a step. However, only the Power Query interface calls this a step. M actually calls this an expression. So that is the term I will use from now on.

There are several fundamental points that you need to be aware of here:

• Each M expression is made up of functions. These can be any of the built-in functions (such as the Table.RemoveColumns) used here—or functions that you have defined (which is explained a little later in this chapter). They can also be calculations or simple logic.

• As you learned in the course of this book so far, data mashup is essentially a series of individual actions (or steps as the Power Query interface calls them). These actions are linked in a “chain” where each expression is built on—and refers to—a preceding expression. In Figure 12-3, this specific expression refers to the output of the #“Changed Type” expression which preceded it.

• M expressions can become extremely complex and include multiple functions—rather like complex Excel formulas. As functions can be nested, this can lead to quite complex expressions.

Writing M by Adding Custom Columns

The best way to understand these columns is probably to see them in action. You can then extend these principles in your own processes. This can, however, be an excellent starting point to learn basic M coding—albeit limited to a narrowly focused area of data wrangling in M.

Initially, let’s perform a column join and create a column named Vehicle, which concatenates the Make and Model columns with a space in between.

1. 1.

   Open a blank Excel file.

    
2. 2.

   Connect to the C:DataMashupWithExcelSamplesBrilliantBritishCars.xlsx data source.

    
3. 3.

   Click Transform to open the Power Query Editor.

    
4. 4.

   In the Add Column ribbon, click Custom Column. The Add Custom Column dialog is displayed.

    
5. 5.

   Click the Make column in the column list on the right, then click the Insert button; =[Make] will appear in the Custom column formula box at the left of the dialog.

    
6. 6.

   Enter & “ ” & in the Custom column formula box after =[Make]. Note the space between the pair of double quotes.

    
7. 7.

   Click the Model column in the column list on the right, and then click the Insert button.

    
8. 8.

   Click inside the New column name box and enter a name for the column. I call it CarType. The dialog will look like Figure 12-4.

    

You can always double-click a column to insert it into the Custom column formula box if you prefer. To remove a column, simply delete the column name (including the square brackets) in the Custom column formula box.

You can see that this line of M code follows the principles that you have already seen. It uses an M formula (Table.AddColumn) that refers to a previous expression (#"Changed Type") and then applies the code that carries out the expression requirements—in this case adding a new column that contains basic M code.

The Advanced Editor

Expressions in the Advanced Editor

The M expressions that you can see individually in the formula bar do not exist in a vacuum. Quite the contrary, they are always part of a coherent sequence of data load, cleansing, and transformation events. This is probably best appreciated if you now take a look at the whole block of M code that was created when you loaded a table from an Excel file previously.

To see the M code, you need to open the Advanced Editor.

1. 1.

   In the Home ribbon, click the Advanced Editor button. The Advanced Editor window will open, as shown in Figure 12-5. You can also see the Applied Steps list from the Power Query Editor to help you understand how each step is, in fact, an M expression.

    

This dialog contains the entire structure of the connection and transformation process that you created. It contains the following core elements:

• A sequence of expressions (which are steps)

• A let expression that acts as an outer container for a sequence of data transformation expressions

• An in expression that returns the output of the entire query

If you look at Figure 12-5, you can see several important things about the sequence of expressions that are inside the Let…In block:

• Each expression is named—and you can see its name in the Applied Steps list.

• Each expression refers to another expression (nearly always the previous expression) except for the first one.

• All but the final expression are terminated by a comma.

• An expression can run over several lines of code. It is the final comma that ends the expression in all but the last expression.

• The final expression becomes the output of the query.

Although this is a fairly simple M query, it contains all the essential elements that show how M works. Nearly every M query that you build will reflect these core principles:

• Have a Let…In block

• Contain one or more expressions that contain functions

Syntax Checking

If you intend to write and modify M code, you are likely to be using the Advanced Editor—a lot. Consequently, it is certainly worth familiarizing yourself with the help that it can provide. Specifically, its syntax checking can be extremely useful and is entirely automatic.

Suppose that you have (heaven forbid!) made an error in your code. The Advanced Editor could look something like the one in Figure 12-7.

As you can see, in this case the Advanced Editor no longer displays a check box under the code and the reassuring message “No syntax errors have been detected.” Instead you see an error message. Clicking the Show error link will highlight the source of the error by displaying it on a blue background.

Basic M Functions

The M language is vast—far too vast for anything other than a cursory overview in a single chapter. Nonetheless, to give some structure to the overview, it is worth knowing that there are a few key categories of M functions that you might find useful when beginning to use M.

I am focusing on these categories as they are probably the most easily comprehensible in both their application and their use. Once you have seen some of these functions, we can move on to other functions from the range of those available.

The result is shown in Figure 12-8.

As you can see, wrapping the Make column inside this particular text function has added an extra layer of data transformation to the expression.

Text Functions

There are, as you might expect, many more text functions available in M. However, the aim is not to drown the reader in technicalities, but to make you aware of both the way that M works and what is possible.

You may well wonder why you carry out operations like this in Power Query when you can do virtually the same thing in Excel formulas. Well, it is true that there is some overlap; so you have the choice of which to use. However, remember that there is no need to copy formulas down over a column in M, as the formula will apply, by definition, to an entire column.

Overall, you can perform certain operations at multiple stages in the data preparation and analysis process. It all depends on how you are using the data and with what tool you are carrying out the analyses.

Table 12-1 is only a subset of the available text functions in M. If you want to see the complete list, it is on the Microsoft website at https://docs.microsoft.com/en-us/powerquery-m/text-functions.

Number Functions

Table 12-2 is only a minor subset of the vast range of number functions that are available in M. If you want to see the complete list, it is on the Microsoft website at https://docs.microsoft.com/en-us/powerquery-m/number-functions.

Date Functions

M has many date functions. Table 12-3 contains a potentially useful sample of some of the available functions.

Time Functions

Table 12-4 is only a subset of the available time functions in M. If you want to see the complete list, it is on the Microsoft website at https://docs.microsoft.com/en-us/powerquery-m/time-functions.

Equally, as they are so similar to the date and time functions, I have not shown here the datetime functions and the datetimezone functions. These are also available on the Microsoft website.

Duration Functions

Table 12-5 is nearly all the available duration functions in M. If you want to see the remaining few functions, they are on the Microsoft website at https://docs.microsoft.com/en-us/powerquery-m/duration-functions.

M Concepts

The time has now come to “remove the stabilizers” from the bicycle and learn how to cycle unaided. This means, firstly, becoming acquainted with several M structural concepts.

So, without further ado, let’s start your journey into M.

M Data Types

If you are creating your own lists, records, and tables, then it will help to know the basics about data types in M.

There are other types such as function, any, or anynonnull, but we will not be covering them in this chapter.

M Values

This fourth bullet point clearly begs the question “what is a restricted character?” The simple answer—that avoids memorizing lists of glyphs—is “anything not alphanumeric.”

Defining Your Own Variables in M

As the values returned by any expression are also variables, it follows that defining your own variables in M is breathtakingly simple. All you have to do is to enter a variable name (with the pound sign and in quotes if it contains spaces or restricted characters), an equals sign, and the variable definition.

As a really simple example, take a look at Figure 12-9. This M script defines the three parameters required for the List.Numbers() function that you will see in Table 12-7 and then uses the variables inside the function.

There are only a few things to remember when defining your own variables:

• They respect the same naming convention as output values in M.

• A variable can be referenced inside the subexpression where it is defined and any expressions that contain the subexpression.

• A variable can be a simple value or a calculation that returns a value.

Writing M Queries

Before actually writing M, you need to know how and where to write your code. Suppose that you need an environment to practice the examples in the remainder of this chapter:

1. 1.

   Open a new, blank Excel file.

    
2. 2.

   In the Data ribbon, click Get Data ➤ From Other Sources ➤ Blank Query. The Power Query Editor will open.

    
3. 3.

   Click Advanced Editor. An Advanced Editor dialog will open containing only an outer let expression, as shown in Figure 12-10.

    

Creating Lists Manually

A list is simply a comma-separated set of values enclosed in braces—such as

{1,2,3}

Once integrated into the structure of an M query, it could look like the example shown in Figure 12-11.

Once created—either as an intermediate step in a query or as the final output of the query—the list can be used by anything that requires a list as its input. Indeed, if you click the Done button for the example shown earlier, the Power Query Editor will display this piece of M code as a functioning list—exactly like the one that you created in Chapter 11. So you can now create custom lists for parameters (for instance) quickly and easily.

There is some technical information that you need to know about lists:

• Lists are unlimited in size.

• Lists can contain data of the same type (i.e., all elements are numeric values, dates, or texts, for instance)—or the data can be of different types.

• Lists can be empty—that is, composed of a pair of empty braces.

• Lists can be entered horizontally or vertically. That is, the list shown earlier could have been typed in as

Source = {

           1,

           2,

           3

         }

Generating lists is really easy; knowing when to use lists is the hard part.

Other Function Areas

Custom Functions in M

M also allows you to write custom functions that can carry out highly specific tasks repeatedly.

When you close the Advanced Editor, you will see that this query has been recognized as being an M function and appears as such in the list of queries. You can see this in Figure 12-12 (where I have renamed it to “DiscountAnalysis”).

You can now invoke the function at any time interactively by entering a value as the discount and clicking the Invoke button. You can also use this function inside other M functions. Indeed, this is probably why you created a custom function in the first place.

Adding Comments to M Code

Complex M code can be extremely dense. So you will likely need ways of remembering why you created a process when you return to it weeks or months later.

One simple way to make your own life easier is to add comments to M code. You can do this both for code that you have written and queries that have been generated automatically.

There are two ways to add comments.

Conclusion

This final chapter completes this book on loading and transforming source data for analysis in Excel. In this chapter, you learned the basics of the M language that underpins everything that you learned in this book up until now.

You began by seeing how you can use the Power Query Editor interface to assist you in writing short snippets of M code. Then you moved on to discovering the fundamental M concepts such as expressions, variables, and values. Finally, you learned about data types in M and the more complex data types such as lists and tables that underlie complex data transformations. This involved learning to use the Advanced Editor to write and debug your code.

In this chapter and the 11 previous chapters, you have seen essentially a three-stage process: first, you find the data, then you load it into Power Query, and from there, you cleanse and modify it. The techniques that you can use are simple but powerful and can range from changing a data type to merging multiple data tables. Now that your data is prepared and ready for use, you can add it to the Power Pivot/Excel data model or directly into an Excel spreadsheet and start using it to deliver real-world analytics.

I hope that you have enjoyed reading this book, and that it will help you to master the art of finding, transforming, and loading external data into Excel using Power Query and M.