Power BI – Power Query M Code Basics

BY: David Rohlfs

Introduction

In this blog we are going to focus on the basics of M Code in Power Query. You can either access Power Query in Microsoft Excel or I will be doing this blog in Microsoft Power BI. For the purposes of this blog there will be no difference which desktop app you choose to use.

This blog will be going over some basic concepts in M Code, but I still wouldn’t suggest this blog for beginners. If you are new to M Code and want to go through this blog I recommend that you have basic experience in some other coding language or have experience in Power BI or Excel. If you do want to start learning a coding language for Microsoft Power BI or Microsoft Excel but you decide not to learn m code, I suggest reading through a couple of EPM Strategy’s blogs on DAX. There will be links below that lead to some blogs that are good for learning basic and advanced concepts.

One thing to note is that you can do many of the main functions in Power Query without knowing M Code. There are a couple of links below that can help you to develop some complex steps without learning M Code.

This blog will be covering formatting, basic syntax, variables, and briefly mention functions in M Code Power Query.

 

Learning the Basics

If you don’t know how to access M Code in Power Query all you need to do is click on Advanced Editor in your Home Ribbon while in the Power Query.

 

A couple of things to note while working in m code is that the text is case sensitive and there is no undo button.

Here is a helpful chunk of vocabulary that you may want to learn before going through the basic concepts below.

Variable

• A variable may contain an action or step that you want to keep separate from a step. This may be because you are using this same chunk of code many times in your script or because you want to clean up your script. A variable is noted by not having the variable name in the following syntax #”Name” .
• Writing in a variable will not change the way that a query appears in the Power Query view.

Applied Steps (Steps)

• A step is like a variable in that it is using a function to work with the data, but the difference is that a step’s purpose is to change the output of the data, not be used in other steps.
• Writing a step will change the output that the query uses. A step will be applied to the reports semantic model (different than just the query preview) once you hit Apply or Close and Apply in Power Query.

Step Name

• A step name is how you will recognize the step that is written after the equals sign (=). If you are not writing your own m code then you may have power query automatically create a name for the step you do, but you have the option to change this if you want to.
• The step name is how you recognize what is happening in the Applied Steps preview.
• A step name will be characterized by using a #”Name” format.
• A variable name is like a step name, but it does not have the #”” formatting, it only uses a set of text.

Function

• A function is a way that a user, Power Query, or a report builder can choose to invoke an action in Power Query. Functions will be covered more in the Function section below.

Formatting M Code

The first concept that we will be going over is formatting your m code script. At the start you will have a “let” statement that signifies the start of your script.

 

After the let statement, you can move into writing your script or variables. Both will be covered in the Basic Syntax or Variables sections.

 

Now you can write your closing statement. All you need to do is go to the next line and write “in” then the name of the ending step.

 

Basic Syntax

The syntax of different functions is different depending on what function you are using, but there is a general syntax for each line of script. It is shown by the code below (for a step):

#”Name” = Function(#”Previous Step”, Function contents),

A couple of things to note is that you use a previous step name for a step (not used on a variable) and that there is an ending comma. This ending comma will be used on all variables and steps except for the step that directly precedes the “in” closing statement.

One thing that is common but is not on all functions is that you can use one function for many columns or alterations to your data. You will probably see this happen with changing type, removing columns, etc. This happens by closing a piece of the statement in the curly parenthesis {} and following it with a comma.

 

The final thing to note is that you must close out all parenthesis, brackets, and curly parenthesis before you can go onto the next step or variable. If you do not, then you will get an error like “Token ‘__’ Expected”.

 

M Code Variables

Variables in M Code can be helpful or can be a waste of time. Here is an example query where I decided to use a lot of variables:

 

In this query I am using the variables to either write in code that would need to be used throughout the script or to make it so that I can layer functions on top of each other. There are sometimes where you won’t be able to layer functions together. This doesn’t always happen, but it can. And when it does, creating a variable is an easy loophole that m code accepts.

If you must repeat the same code multiple times, then it may be advantageous to write a variable that contains that code. In the example above I used the Row variable three times in only ten lines of code. This saves a lot of time and makes it so that if I want to change that variable, I only need to do it in one spot.

A trick with variables is that you can create a set of steps with only one applied step. You do this by layering the variables together and having a step that calls the final variable. Look at the screenshot below and see that Row and TableFilter go into Source then Source goes into Result that is used in #”Transposed Table”. The only step in this set of script is the #”Transposed Table”.

 

Functions

We will not be creating a function in this blog, but I want to go over why they differ from a normal set of m code.

 

In this set of m code, you can see that there are two sets of let and in. Doing this makes a function that a user can plug information into and run it through the second set of code and create an Invoked Function Query. If you are new to m code, I don’t recommend trying to write this function, but I wanted to show you that you could potentially have a script that does not follow the rules stated in the Formatting M Code section above.

Common Problems

The main problem that you will encounter with m code is that you break your own query. This will become obvious to you when you see your code appear in the query preview page or with the big red X in the applied steps. If this happens do not click on the big red X in the applied steps. This will wipe your applied steps and you will have to restart. There is no undo button for this besides a previous save. What you will want to do is to go back into the applied steps and find what mistake you made and correct it. If you go line by line and look for something like a misplaced comma or parenthesis you will probably find it quickly.

If you are fortunate enough, then Power Query may show you in your Query page where it sees an error. This may not be where your error really is, but it will give you hints for what area and what it might expect.

 

Tips and Tricks

You do not need to keep your code in order from first to last, but you should. You can write variables and steps that are applied at different parts of your steps, but it is standard to write your steps in order and your variables either in order of use or at the beginning or your script.

You can write in notes to your m code. If the note is in a single line, then you can preface the beginning of it with two backslashes (//). Or if the note is more than one line long you can use a backslash asterisk as the beginning point and an asterisk backslash as the ending point. If you do this correctly then the notes will appear green while in the advanced editor. The notes that you write in will be shown in the subsequent code by an information symbol in the applied steps pane.

 

Before you click Done or Close on the Advanced Editor page, you should check that there are no syntax errors. You can see this by looking at the bottom left corner of your page.

 

If there is an error with the syntax it will show something like this:

 

Sometimes the Show Error button helps, but it doesn’t always help much.

 Conclusion

Beginning to learn M Code is difficult and very intimidating, so if you are I recommend trying some test queries. But once you learn to embrace the difficulty and find some tricks for writing in M Code it can become a huge time saver and help with any query in Power Query. If you choose to not learn M Code, I recommend going through some of EPM Strategy’s blogs on DAX below. DAX is easy to learn and much more forgiving than M Code.

Links to Related Material:

Power BI Round Function in DAX : :: Welcome To EPM Strategy ::

Power BI Switch Function in DAX : :: Welcome To EPM Strategy ::

Power BI Understanding the Basics of Using Variables in DAX : :: Welcome To EPM Strategy :: Power BI Column From Examples : :: Welcome To EPM Strategy ::

Power BI Conditional Column : :: Welcome To EPM Strategy ::

Comments