Basics of M: Power Query Formula Language (2024)

M is the powerful language behind the scene of Power Query. Any transformation you apply will be written in M language. For many, M looks like a scary language. In this post I like to explain a bit of basics of M. Not mentioning any functions. Mainly I want to explain to you how the M language syntax is structured. Once you know the syntax, then everything becomes simple. M is a language that you can learn it’s syntax easily. As a Power Query developer; I highly recommend you to spend time on M, because there are MANY operations that you can with M, but you might not be able to do it simply with the graphical interface. If you would like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

M is informal name of Power Query Formula Language. The formal name is so long that no one uses that, everyone call it M! M stands for Data Mashup, some say stands for Data Modeling. M is a functional language, and it is important to know functions of it. However, each language has a structure and syntax which is the beginner level of learning that language. In this post, I will explain the syntax of M. Before learning M, I would like you to read this sentence loud;

M is much more powerful than the graphical interface of Power Query

Yes, you read it correct! The graphical interface of Power Query is changing every month. Every month new functionality comes to this graphical interface. But the fact is all of these functionalities has been in the language for many years! if you knew the language, you could easily use them, instead of waiting for graphical interface option for it. There are heaps of examples for it. one very small example is here: you can extend your Grouping ability in Power Query with a very minor change in M script of it.

Syntax of this language is simple. it always has two blocks of programming: LET expression block, and IN expression block. Here is the most simple M syntax;

let x=1in x

let and in are reserved words. before going even one step further, the first and foremost thing you need to know;

M (Power Query Formula Language) is Case Sensitive. There is a difference between x and X.

what are these two programming blocks:

let: definition of all variables

in: output! Yes, in actually means out! just named as in. everything you put in this block will be the output of your query.

So basically, the query below means defining a variable named as x, assigning the value 1 to it, and showing it as the result set. so the query will return 1.

to run this example, you need to Open Power BI Desktop. Go to Get Data, start with New Blank Query.

then in View tab, select advanced Editor;

Make sure when you write the script that you put reserved words such as let and in all lowercase. also your variable name should be the same case in both let and in section.

As you can see, there is no need to define data types for variable. it will be automatically assigned when the first assignment occurs.

If you specify a text value, then variable would be a text data type automatically.

Lines of codes in M continues if you don’t put the end of the line character.

As you can see in above example, the line continues, and x will be equal to x=1+1 . If you want to put an end for a line use comma(,). example here:

every line needs a comma(,) to finish. except the last line before in.

Name of variables can be all one word, like Source. or it can has spaces in it. in case that you have some characters such as space, then you need to put the name inside double quote (“) and put a hashtag at the beginning of it(#). something similar to:

#"This is a variable name"

Variable name can contain special characters, here is an example:

Special characters

Variable names can have special character, as you can see below variable has all types of characters in it and still runs good.

Escape character

Double quote (“) is escape character. you can use it to define variables with names that has another double quote in it. here is an example:

first double quote (highlighted) above is necessary to be before the second double quote (which is part of the variable name).

Power Query is a step by step transformation. Every transformation usually happens in a step. While you are writing the code, you can also notice that in the right hand side, you will see every variable forms a step.

in screenshot above, you can see every variable is determined as a step. and if the variable has space in the name, it will show it with spaces in list of applied steps.

The last variable is always specified in the in section.

There are different ways of defining every literal in Power Query. For example, if you want to define a date variable, here is how to do it;

for defining all other types of literals, here is the reference table to use:

* for function and type; I’ll write another post later to explain how these types works.

M is a functional language, and for doing almost everything you need to call a function for it. functions can be easily called with name of the function and specifying parameters for it.

screenshot above uses Date.Year function which fetch year part of a date. Functions names starts always with capital letters: Date.Year()

like any programming language, you can put some comments in your code. it can be in two forms;

Single line commentary with double slash (//)

Multi line commentary between slash and starts (/* comments */)

Now that you know some basics, let’s look at an existing query in advanced editor mode and understand it.

in screenshot above, you can see all basics mentioned so far:

1. let and in block
2. variable names matching steps applied in the query
3. some variable names with hashtag and double quote: #”var name”
4. end of the line characters: comma
5. calling many functions

There are still many parts of this query that you might not understand. specially when using functions. you need to learn what functions are doing in order to understand the code fully. I have written a blog post, that explains how to use #shared keyword to get documentation of all functions in Power Query.

In next posts, I’ll explain another levels of structures in M.