M Language Explained in Power BI
An Introduction to M
The M Language in Power BI transforms and imports data in Power BI. It is the backbone of the Query Editor from the data import stage into Power BI to powerful transformations in Power BI. At first glance, the M Language in Power BI might seem overwhelming, but on closer inspection, it is logical and can be learnt easily. We suggest to any Power BI developer to understand M very well as this enables you to perform powerful calculations and transformations in Power BI. The M Language in Power BI also has a set of functions that is very useful to understand in transforming data. A whole new world and ease of using advance functionality become available as your understanding of M expands.
M as a Query Language
We will start by giving an overview of the basics of M, which must be grasped before moving on to the rest. Below is a representation of M in the Power Query Editor in Power BI.
As seen in the above image, the M function has two main ‘bodies’ the ‘let’ and the ‘in’ body. Before explaining ‘in’ and ‘let’, it is important to note that the M language is case sensitive. Both ‘let’ and ‘in’ is in small caps and won’t have the same meaning when not written in small caps.
The let block defines and calculates and compiles all variables.
The ‘in’ block is the output of all the calculations. Yes, it sounds strange that ‘in’ means output, but that is exactly what it is.
Now that we have an idea of what ‘let’ and ‘in’ means we can take a closer look at other details.
End of line
A comma ends a line of code in M. If there is no comma in place the code will just continue. Let’s look at an example:
y = 1
The above M Language code will return a result of 1.
If we write the following M Language code:
y = 1
The output of the above will be 2. If you look carefully we did not end the line with a comma, thus M language just keeps on reading the code and returns a value of 2. Let’s look at an example where we introduce a comma and add a second line of code to our query.
y = 1,
x = y + 20
The result of the above code is 21 as our variable y is equal to 1 and to determine x we added 20 to y.
Functions in M Query Language
In the Power Query M formula language, a function is a mapping from a set of input values to a single output value. A function is written by first naming the function parameters, and then providing an expression to compute the result of the function. Functions gets defined and invoked in the body of a let statement. A function is a value just like a number or a text value and can be included in-line just like any other expression.
In our example, our function is Table.PromoteHeaders. This function promotes the first row of values as the new column headers (i.e. column names). By default, only text or number values are promoted to headers. In the example provided it was necessary to promote the headers as the first row of our data was the column number and not the column name.
After the addition of Table.PromoteHeaders we are returned with our column names that were in the first row of our table as column headers.
Visit Power BI’s page where they outline all the functions available in Power BI.
Variable Names in M Query Language
Variables are also important to understand better. As in our examples above we used simple variables such as x and y. From our example out of Power BI we see that we have a variable called #”Promoted Headers”. Variables may take on any format. It can be one word such as ‘promoted’ or it can be multiple words and must be enclosed by double quotations (“).
Using M Query as a Custom Column
Now that we have a better understanding of M in the Query Editor, we need to get a better understanding of using M as a language for custom columns and how this is populated in the Query Editor. Let’s continue with our example. For more on transforming columns please visit our blog post on the transformation of columns.
M Query Example
From our sales table, we would like to see if our sales price falls into a certain bracket. Our brackets will be below $10, below $20 and above $20. We can achieve this by creating a custom column in our query editor and using the Nested if statements in the M language.
Select the custom column:
Once the custom column is select a window will open where you can add your M language code to achieve the desired results. You can enter a name for the column. On the right-hand side, you see all the fields in your table that you can drag across for your calculations and the middle block is where you add your M language code.
Let’s examine the below nested if statement.
The if statement is followed by a conditional statement “[Sales Price] <=10”. In our case, we tested if our column value ‘[Sales Price]’ meet a certain condition. The first if statement tests if our sales price is less than 10. If the statement is less than 10 then a text value named “less than 10” is returned. If the first if statement is not met we move on the ‘else if’ statement that will test the following condition, is the value less than $20. The formula will test the condition for each row in our table to return the corresponding value.
The custom column will return a new column in our table as shown below:
Let’s take a closer look at how this populates our Query Editor:
- The variable name in the ‘let’ body is ‘#”Added Custom”.
- The function is ‘Table.AddColumn’. This function calculates our new column.
- We can see that in the first statement of our function we call the previous variable called ‘#Promoted Headers’. The second statement in our function is our nested if statement, the expression to compute the result of the function.
This should shed some light on how the backend of the Query Editor works with the available functionality in the Power BI, such as adding a custom column. We used the custom column functionality to create a new column, but we could have written the M code straight in the Query editor to obtain the exact same result.
Comments in M Query Language
Providing comments in your code in any language is very valuable to make sense of what you are doing. This is especially relevant when there are more complex calculations. In M there are two types of comments:
- A single-line comment
- A multi-line comment
To define a single-line comment use a double slash ‘//’.
To define a multi-line comment use a opening slash and star ‘/*’ and a closing star and a slash ‘/* (/* insert comments */).
This was an introduction to M and should shed some light on the function of the M language. Although there are many more aspects to the M language it is essential to grasp the basics explained in this post. As with any language, it is important to self-study and practice in order to get familiar with the working thereof. This is the first post on M language for the Databear team, but be sure to keep on following us for more content on this matter.
The Data Bear team has more posts which you can find here.