Power Query M language – basic principles – Trainings, consultancy, tutorials

Power Query M language – basic principles

This article describes basic rules for working with M, which is a language for queries in Power Query (Get and Transform) as well as in Power BI.

Honestly, it is not necessary to know this language – because most of workflow steps can be done in standard GUI and M is used in background only. So, this article is mostly for advanced users – that need to write less common thing.

Before I start with specific rules, I´d like to emphasize that M is case sensitive in both variables and function names. So, for example when writing “If” function, you cannot write “IF”. This is little bit tricky, since other languages we are discussing on this webpage are not so sensitive in this delicate in this…

Common formulas in Excel can be written in capitals or small letters, anyway Excel changes everything to capitals. For example “if” is changed to “IF”.
In DAX you can use small or capitals and it is OK
VBA changes the syntax automatically – in functions mostly to first capital

So we have to care about case sensitivity in M. And what else?

Basic logic

The basic logic is quite simple. There are always two blocks – “let” and “in”. “Let” contains multiple rows defining variables, “in” is an output. Yes, “in” is really the output – I don´t say this syntax makes sense…

It looks like this:

let
my_variable = “hello world”
in
my_variable

In this example, the output is “hello world”, the value of “my_variable” variable, which is in output. If you write this in the advanced editor in Power Query or in Power BI, you will really see the text string “hello world”.

Yes, but don´t need the “hello wordl” usually. So this is a query, taking the “Revenue” column in “Source” source and divides it by 1000.

let
Source = my_source,
#”Divided Column” = Table.TransformColumns(Source, {{“Revenue”, each _ / 1000, type number}})
in
#”Divided Column”

Variables

To say it simply, every row is derived from the previous one. So the last row´s name is “Divided Column” and is calculated from “Source” using the Table.TransformColumns function. Source comes from “my_source” etc, no matter how many rows do we have.

You can give any name to the steps (names of variables). So if you switched “Source” to “xy”, it is still working – you just have to change twice – in two following rows.

If the name of variable contains multiple words separated by space, you need to start with # and use quotation marks. For example:

but:

#”x y”

Functions

Functions are called simply by writing of their name and its arguments, like this:

Text.Start(“abcd”, 2)

resulting in “ab”

Comments

Comments, the part of code, that has no impact on query, can be written like thiswith two slashes (if it is only one row):

// this is my comment

or like this (if it is for multiple rows)

*/ this is my
comment */

So… that´s all…

This article describes basic rules for working with M, which is a language for queries in Power Query (Get and Transform) as well as in Power BI.

Honestly, it is not necessary to know this language - because most of workflow steps can be done in standard GUI and M is used in background only. So, this article is mostly for advanced users - that need to write less common thing.

Before I start with specific rules, I´d like to emphasize that M is case sensitive in both variables and function names. So, for example when writing "If" function, you cannot write "IF". This is little bit tricky, since other languages we are discussing on this webpage are not so sensitive in this delicate in this...

Common formulas in Excel can be written in capitals or small letters, anyway Excel changes everything to capitals. For example "if" is changed to "IF".
In DAX you can use small or capitals and it is OK
VBA changes the syntax automatically - in functions mostly to first capital

So we have to care about case sensitivity in M. And what else?

Basic logic

The basic logic is quite simple. There are always two blocks - "let" and "in". "Let" contains multiple rows defining variables, "in" is an output. Yes, "in" is really the output - I don´t say this syntax makes sense...

It looks like this:

let
my_variable = "hello world"
in
my_variable

In this example, the output is "hello world", the value of "my_variable" variable, which is in output. If you write this in the advanced editor in Power Query or in Power BI, you will really see the text string "hello world".

Yes, but don´t need the "hello wordl" usually. So this is a query, taking the "Revenue" column in "Source" source and divides it by 1000.

let
Source = my_source,
#"Divided Column" = Table.TransformColumns(Source, {{"Revenue", each _ / 1000, type number}})
in
#"Divided Column"

Variables

To say it simply, every row is derived from the previous one. So the last row´s name is "Divided Column" and is calculated from "Source" using the Table.TransformColumns function. Source comes from "my_source" etc, no matter how many rows do we have.

You can give any name to the steps (names of variables). So if you switched "Source" to "xy", it is still working - you just have to change twice - in two following rows.