Sign in or start a free trial to avail of this feature.
10. The M Formula Language
Underlying all the actions we take in the Query Editor is a formula language called M. In this lesson, we will look at the M code behind our query and discuss when using M is a good or bad idea.
M Formulas in the Query Editor
- A query is made up of multiple steps, and each step has a corresponding M formula
- When we work in the Query Editor, an M formula is created for every step of the query
- Enabling the formula bar and studying the M code for each step of your query can be a useful way to learn the basics of the language
- The Advanced Editor displays the M formulas for all of the steps in a query
- Each step refers to the previous step, so the query builds up as all the steps are executed
As we've seen in this series of lessons, the query editor allows you to extensively shape and transform your data without writing any formulas or code. However, the query editor does have its own formula language known as M.
In this lesson, we'll look at how queries in the query editor are built using M formulas.
We won't worry about learning specific M formulas for now. Instead, we'll focus on the structure of the M language using creating queries.
As a first step, we'll turn on the formula bar. We'll navigate to the view tab and check the formula bar box.
We now see a bar that is very similar to the formula bar in Power BI or Excel.
On the right of the screen, we can see the query settings pane which lists all the steps we've taken since importing this data set. As we click through the various steps, we can see that each step has its own M formula. For every action we've taken, an M formula has automatically been generated.
We can view the M code for the query as a whole using the advanced editor. We can access this from either the home tab or the view tab. Let's go to the view tab and select advanced editor. This shows us the M formula for every action in the query. As we have a rather large query, this looks like a daunting block of code. Let's create some white space and focus on the last two commands representing the two columns we created in the previous lesson. Again, don't worry about the specific formulas for now. We're just looking at the structure. Notice each step has a name enclosed in double quotes and proceeded by a hash symbol. If the step name has no spaces, then the quotes can be left out. After the equal sign is the formula itself. Notice also that each step includes the name of the previous step. This is how code is built in M. The query is built up iteratively with each function building on the previous line, allowing you to follow the sequence all the way back to the start. Let's look at the structure of the query as a whole. As we can see, the various steps in the query are contained in a let expression. The in statement at the end of the query defines the output from the query. In this case, it's the name of the final statement. Remember, the final statement refers to the second to last statement and the second to last statement refers to the third to last statement, all the way back to the beginning of the query. This means that the query will execute all of the statements we can see here in the order they are listed. Finally, it's worth noting that you can add comments in the advanced editor. Any line that starts with a double forward slash is a comment and will not be evaluated when the query is run. If you or your colleagues are going to edit M formulas, you should always add comments to the code. I'll add some comments and white space off camera to make the query look a bit nicer.
You might be curious about the relevance of M. By learning M, you can shape your date in almost any way. If you have a transformation in mind that's not available through the query editor interface, you can simply rate a piece of M code that will do the job. Before going down this road, don't forget what the query editor is for.
Its main purpose is to get data from various sources and transform it into a useful format for analysis. Although you could use M to create new columns that perform various calculations on your data, this is not always appropriate. You're generally better off doing your actual data analysis in Power BI. Let's stop the lesson here. We've seen how the M language is used to create all of our queries in the query editor. If you want to learn more about M functions, you can consult Microsoft's M language reference linked in the lesson notes. This includes some of the more technical aspects of the language as well as the complete reference to all the functions available in M. In the next lesson, we'll look at how we can edit our queries and load them to Power BI.