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:
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:
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:
What are curly braces and what do they do? – Curly braces look like this: {}
These curly braces denote the presence of a ‘list’, one of the main building blocks of M code. A list is what it sounds like – a list of data which can be of any data type. A list of information can be generated by inputting the values of data between two curly braces like this:
{1,2,3,4,5}
What are square brackets and what do they do? – Square brackets look like this: [ ]
Square brackets indicate a ‘record’, another of the main building blocks of M code. For the moment, records are like rows of information
Data types in functions – You may have noticed in the Table.SelectColumns syntax, the word ‘as’ quite a lot.
Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table
These words define the data format that the input into a parameter must be in for the function to work. For example, we see the words ‘table as table’ in the first parameter of the Table.SelectColumns function. This is telling us that the input that we put into this parameter, must be a table (rather than a list or record for example)
We then have ‘columns as any’. This means that we can input any columns here regardless of what their data type is, so we could select a column formatted as text, a column formatted as dates and one formatted as numbers)
Finally we finish with ‘as table’. This means that the function will return a table, but we could have other values such as ‘as list’ or ‘as logical’, which would return a list, or the logical values ‘true’ and ‘false’ respectively.