M language in Power Query: Part I-From M to Mastery-An introduction

M language or Data Mash up language is used in Power Query Editor of Power BI desktop and is designed for data cleaning, transformations and creating custom columns. The engine behind Power Query Editor is Mashup engine which executes the code after evaluation. This engine helps the user to perform pivot/unpivot, replace values, transposing of tables and various other functions in Power Query Editor.

We will solve some scenarios using M language to get an idea about this language and then we will study important functions and their usage in M. We will learn the full potential of this language to optimize the cleanings and transformations before loading the data into Power BI desktop.

An important point to keep in mind is that M language is case sensitive. Although in new updates of Power BI, many important things can be done in one or two clicks, yet, if you want to go beyond that for data cleaning, understanding what M language is doing behind the scenes is essential.

Structure of an M formula:

Like in any other language, M language is a combination of functions and operators to perform various calculations, aggregation, etc.

Various types of M language functions are:

1. Mathematical functions

2. Text Functions

3. Aggregate functions

4. Logical functions

5. List functions

6. Date/Time functions

Operators include Arithmetic, Logical, and Comparison.

Data Types and Variables in M:

Data types in M language include Boolean, Binary, Date, Datetime, Text and Number.

Let's look at some scenarios for using M language.

The dataset we will be using looks like this.

Scenario 1: Calculate Pulse Pressure of patients in power query editor, given SBP and DBP.

Here we are given columns SBP and DBP. Now we have to go to 'add column' and select 'custom column' in Power Query Editor and give the name of the new column and formula.

Scenario 2: Find week of pregnancy of onset Gestational diabetes in patients greater than 30 years of age.

Here we are taking OGTT values to determine gestational diabetes in 0th hour. The conditions for GDM by American Diabetes Association are:

Fasting - 5.1 mmol/L or higher.

1st hour- 10.0 mmol/L or higher

2nd hour-8.5 mmol/L or higher

Let's see how we can catch the week when GDM (Gestational Diabetes Mellitus) started.

Here you can see the new column is showing 'Error' and that is because there are null values and we have to deal with null values at the time of calculation.

Let's change the formula to handle null values.

We are able to find the week of onset GDM.

Scenario 3: Does severe Gestational Diabetes is a cause of birth injury or stillbirth?

Here we have to take two conditions with respect to GDM. Let's create a new column for this.

These are a few scenarios where we can use the M language in Power Query Editor to create new columns.

M language has many functions which play a pivotal role to empower analysts to go beyond basic capabilities of power BI.

Thanks for reading.