An Introduction to PowerQuery Functions

After a good rundown of some basic principles of M code in the last lesson, we’re going to look at the structure of some basic functions and their structure in this lesson.

A quick recap from the last lesson:

Every line of code in the PowerQuery editor equates to a step in the Query Settings pane. This line of code is like a calculation which follows the following basic pattern:

To get Table X, perform a specified function (such as a function to remove rows or columns) over a previously defined table using the function’s parameters

Here then is some example code…

#”Duplicated Column” = Table.DuplicateColumn(#”Changed Type with Locale”, “Actual arrival date”, “Actual arrival date – Copy”)

…and if we try to map that code to the basic pattern, we get the following:

To get Table X  = the table that is being created is called #”Duplicated Column”.

Perform a specified function = the specified function is called Table.DuplicateColumn

Over a previously defined table = the previously defined table in the code is called #”Changed Type with Locale”

Using the function’s parameters = here are the remaining parameters of the function: “Actual arrival date”, “Actual arrival date – Copy”

To put the meaning of that piece of code together, it says:

To make the table #”Duplicated Column”, take the table #”Changed Type with Locale” and then use the function Table.DuplicateColumn over that table. This will then create the table #”Changed Type with Locale” but with a duplicated column. The parameters of the function to make that Duplicated Column, is:

1. The name of the column to be duplicated which is Actual Arrival Date
2. The name of the resulting duplicated column which is Actual arrival date – Copy

The table #”Duplicated Column” is therefore the previously defined table #”Changed Type with Locale” but with a duplicated column called Actual arrival date – Copy.

Where can I find a list of functions?

So let’s talk about the functions that we can use. If you’d like a full list, then that can be found at the below Microsoft website address:

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

On left hand side of this webpage we can see that the functions are divided into a number of subsections including functions relating to a whole table, date data, datetime data, text data, splitting data and so on.

Each function is made up of two sections separated by a full stop. The first section preceding the full stop is the name of the subsection, for example Table, Date, DateTime or Splitter, while the second section is what the function actually does. Here are some examples of how this structure looks:

Table.DuplicateColumn = This function is part of the Table subsection and duplicates a column from the table specified as one of the functions parameters
Text.End = This function returns the text of a specified amount of characters from the end of the text – much like the RIGHT function in Excel
Number.From = Converts number characters formatted as text into numbers for the purpose of a function.

Capitalisation of functions:

The first thing to point out here is that you must ensure that every word in the function is capitalised. If the function isn’t spelt correctly or capitalised, then it won’t work and you’ll get an expression error.

The Table Function structure

As previously noted, there are different types of functions including (among others) Table, Text and Number. Table functions are arguably the most important function as every step will start with a Table Function such as Table.SelectColumns or Table.ReorderColumns. These Table functions follow a specific pattern:

1. Start with the Function Name
2. Open the brackets and then put in a previously defined table name. If the table name has spaces in it, then don’t forget the hashtags and speechmarks surrounding the table name.
3. Each function will then have specific parameters that need to be included. For example, let’s take the function Table.SelectColumns. Below is the function syntax available on Microsoft’s function page:

       Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table

The parameters here are Columns as any and Optional missingfield as nullable number.

For the Columns as any parameter to work, we need to put in the list of columns that we want to select. A list in PowerQuery needs to be surrounded by curly braces ({}). Each name of each column is surrounded by speechmarks and separated by commas. Below is an example of how this part of the code could look.

{“Container number”, “Type”, “Booking date”}

For the Optional missingfield as nullable number we can put in a function to describe what to do if a column that we pit in our Columns as any parameter does not exist. We can use for example MissingField.UseNull.

Overall then, a piece of code using Table.SelectColumns to remove non relevant columns from the Source step could be:

#”Select Relevant Columns = Table.SelectColumns(Source, {“Container number”, “Type”, “Booking date”}, MissingField.UseNull)

Other function types

Once we’ve used a table function to get us started in a step, then we can also use other types of functions within our code. Some of the more common ones that you’re likely to come across are Text, Number, Replacer, Splitter and Date functions.

These generally don’t require the name of a table as part of the parameters, but instead require specific values or columns, where you’d like to look at data row by row within a specific column.

 There are a huge amounts of M code functions that can be employed so if you are interested in being able to:

• amend code that the ribbon gives you,
• debug code (pretty mandatory if you’re building reports that will be relied upon) or
• build code from scratch then do look at the Microsoft function page to see what’s possible.