Power BI MCSA Certification Tips – Part 1 : M Language Overview
To obtain your MCSA: BI Reporting you need to pass two exams: 70-778 (Analyzing and Visualizing Data with Power BI) and 70-779 (Analyzing and Visualizing Data with Excel). However, there is a huge overlap between the two exams resulting from the common BI features found in both Power BI and Excel: namely data modelling using the DAX language; and data connection and transformation using the M language.
We, at G Com Solutions, recommend to the clients to whom we supply Power BI Training that they prepare for both exams simultaneously and then sit both exams on the same day. We also run a 5 day Power BI MCSA Certification Prep course in London which includes both exams 70-778 and 70-779.
In this series of blog posts, we will be examining some of the key features of Power BI, Power Pivot and Power Query which you will need to master in order to pass the two exams.
One of the features of both the 70-778 and 70-779 which surprises many candidates is that many of the questions on transforming data often refer not the Power Query interface commands, but to the underlying M language syntax. To pass the exams, you will not need to become fluent in writing M code; however, you should be prepared for M language questions which require you to understand M syntax.
Let us, therefore, begin our certification prep blog posts with a brief introduction to the M language.
Working with M in the Query Editor
When working in the Power BI Desktop Query Editor, or in the Power Query Editor in Excel or in the cloud, queries can be designed by choosing visual commands which generate a series of steps, displayed in the Applied Steps pane, on the right of the Query Editor window.
However, Power Query also allows you to work with the underlying M code. Firstly, you can edit M code by clicking Home > Advanced Editor. Secondly, you can use the formula bar to edit the M code for the item currently selected in the Applied Steps pane. And, thirdly, you can write M code in certain dialogues when creating certain steps; for example, Add Column > Custom Column.
The Query Editor basic coding assistance in the form of colour coding and code completion; and we can assume that these features will be added to the Excel Power Query Editor in due course.
The nature of the M language
M is a case-sensitive functional programming language; this means that it consists of a series of expressions (stored in variables) and produces results by the evaluation of these expressions.
The let … in statement block
let is a block statement which can contain a series of expressions. Each line contains a separate expression, in the format:
Variable1 = Expression
Variable2 = Expression
Each line inside a let block, except for the last, must end in a comma.
Variable names appear as named steps in the GUI.
The expression referenced by the in statement is returned as the result of the query.
Comments in M
Single line comments are created using //, e.g.,
// Remove blank rows
Multiline comments created using /* */, e.g.,
/* Connect to current year budget if available
Use last year’s if not found
*/
Variables in M
Variable names normally contain no spaces and start with a letter or an underscore, e.g.,
Source = Expression
Variable names can contain spaces if they are enclosed inside #” “, e.g.,
#”Remove Errors”, = Expression
Built-in (primitive) data types
· Any, none, null
· logical
· number
· text
· binary
· time
· data
· datetime, datetimezone, duration
Complex data types
Lists
A list is essentially equivalent to the array construct found in most programming language. Lists may be returned as the result of an operation or created manually. The standard way of accessing an item in a list is to use a zero-based index in curly braces. Thus, to access the first item, we could use the following syntax:
let
ListOrArray = {“Trump”, “Merkel”, “May”, “Macron”},
Item1= ListOrArray{0}
in
Item1
Records
A record differs from a list in that each item is named. The following code creates a record and then searches for an item within it:
let
MyRecord = [
USA = “Trump”,
Germany = “Merkel”,
#”United Kingdom” = “May”,
France = “Macron”
],
ItemSearched = MyRecord[Germany]
in
ItemSearched
Tables
Many of the operations performed in M, for example the data access functions, return a table. Unlike lists and records, tables cannot be defined as literal expressions. However, the function #table can be used to define a table structure.
let
Leaders = #table(
{“Country”, “Leader”},
{
{“USA”, “Trump”},
{“Germany”, “Merkel”},
{“United Kingdom”, “May”},
{“France”, “Macron”}
}
)
in
Leaders
Tables are the most important of the complex objects you will encounter in the M language since most of your efforts are aimed at producing tabular data. Tables can be viewed as being composed of either lists or records, using the following logic:
· A table is a list composed of a series of records (rows)
· A table is a record composed of a series of lists (columns).
Accessing a table row as if it were an item in a list
Given the above statements, we can access any row in a table by treating the table as a list and specifying the index of the element (i.e., the row) which we want to retrieve. Thus, to retrieve row two of the table, we would use the following syntax:
let
Leaders = #table(
{“Country”, “Leader”},
{
{“USA”, “Trump”},
{“Germany”, “Merkel”},
{“United Kingdom”, “May”},
{“France”, “Macron”}
}
),
Leader2 = Leaders{1}
in
Leader2
Note that the item returned is a record, not a table row.
Accessing a table column as if it were an item in a record
In a similar way, we can access any column in a table by treating the table as a record and specifying the name of the element (i.e., the column) which we want to retrieve. Thus, to retrieve the Leader column of the table, we would use the following syntax:
let
Leaders = #table(
{“Country”, “Leader”},
{
{“USA”, “Trump”},
{“Germany”, “Merkel”},
{“United Kingdom”, “May”},
{“France”, “Macron”}
}
),
LeaderCol = Leaders[Leader]
in
LeaderCol
Note that the item returned is a list, not a table column.
Accessing a single cell in a table
If we first access a table row and then access an item in the resulting record, we can access the value of a single cell within a table. Thus, to retrieve the name of the second country in the table, we can use the following syntax:
let
Leaders = #table(
{“Country”, “Leader”},
{
{“USA”, “Trump”},
{“Germany”, “Merkel”},
{“United Kingdom”, “May”},
{“France”, “Macron”}
}
),
Country2 = Leaders{1}[Country]
in
Country2
This syntax is used very frequently in the M language code generated when you use the Get Data command to connect to data sources.