Power BI - Explain the 'M language'

In Power BI data preparation is a critical step in building accurate and meaningful reports. The M language used within Power Query plays a key role in this process by defining how data is connected, cleaned, transformed and combined from different sources. It allows users to create repeatable and refreshable data transformation logic, making data models more reliable and efficient.

Implementation

You can download dataset from here

Power Query M formula language

Power Query provides a useful data import and transformation experience across tools like Power BI and Excel. Its core capability is to clean, transform and mash up data from multiple sources using the M Formula Language a functional and case-sensitive language designed for data preparation.

• M language defines all data transformation steps in Power Query
• It is a functional case-sensitive language similar to F#
• Supported in Power BI Desktop and Excel (Get & Transform)
• Transformations are reusable and automatically refreshable

Power Query M language specification

The Power Query M language specification formally defines how the language works including its syntax, structure and evaluation rules. It explains how M code is written, interpreted and executed to transform and shape data reliably.

• Defines core concepts such as values, expressions, variables, identifiers and the evaluation model
• Describes primitive and structured values with types treated as special values carrying metadata
• Explains operators, expressions and a rich standard library of functions
• Includes error handling, let expressions, conditional logic and modular grammar definitions

Creating Power Query M formula language queries

1. First, open the Power Query Editor in Power BI Desktop as shown below.

2. Open the Get Data tab or go to Recent Sources, select the required dataset and click Transform Data to open it in Power Query for cleaning and shaping the data.

3. This will open the Power Query Editor for data transformation.

4. Power Query is where M language is used and on the right, you can see the four automatically generated steps.

5. From now on any changes you make will be recorded as steps. For example right-clicking a column and selecting Remove Other Columns.

6. You’ll see this step appear here as Remove Other Columns.

7. Next we can change the text to lowercase by going to the Add Column tab and clicking Format then Lowercase.

8. Performing this action creates a Lowercase step with the M function visible in Table.AddColumn.

9. The Query Editor lets you create complex queries. A let expression contains variables, expressions and values and you reference a variable using #"<Variable Name>".

This structure is followed by a let expression:

let  
  Variablename = expression,  
  #"Variable name" = expression2  
in   
  Variablename

In our case the Advanced query formed can be seen in Advance editor tab:

As a result the procedures below can be broadly applied to creating a M query in the Query Editor:

• Create a series of query steps starting with a let statement. Each step is identified by a variable name and you can use #"Step Name" to include spaces. Each step can contain a unique formula and M language is case-sensitive.
• By referring to a step by its variable name, each query formula step builds on the one before it.
• Use the in statement to output a step from a query formula. The final query step result is typically utilized as the final data set result.

DAX vs M Language

Power BI uses both DAX and M Language to manage, transform and analyze data. While they may appear similar they serve different purposes operate at different stages of the data workflow and follow distinct syntax and logic.