Power BI – Explain the ‘M language’

A robust “get data” experience with many options is offered by Microsoft Power Query. The ability to filter and mix, or “mash-up,” data from one or more of the many supported data sources, is a fundamental feature of Power Query. Using the Power Query Formula Language, such data mashups are expressed (informally known as “M”). Excel, Power BI, Analysis Services, and Data-verse are just a few of the Microsoft tools that Power Query integrates M documents into to enable repeatable data mashup.

Following DimDate sample Dataset have been used to perform M Formula Language Queries.

Dataset

Power Query M formula language

Several elements are included in the robust data import experience offered by Microsoft Power Query. Workbooks from Analysis Services, Excel, and Power BI are compatible with Power Query. Power Query’s primary functionality is the ability to filter and mix, or mash-up, data from one or more of the many supported data sources. The Power Query M Formula Language is used to express any such data mashup. It is a functional language with case sensitivities, akin to F#.

Similar to F# in functionality, Power Query in Excel, Get & Transform in Excel 2016, and Power BI Desktop all support the M language. Only the Power Query Editor in Power BI supports the M language.

What are the main differences between DAX and M Language?

Power BI supports M Language and DAX to manage, manipulate, filter, and analyze the data. However, they are distinct from one another, are not interdependent, and each has a unique syntax, structure, and logic.

DAX	M Language
Each of the roughly 250 functions used in Data Analysis Expression (DAX) formulas is covered in full in the DAX function reference, which also includes syntax, arguments, return values, and examples.	Each of the roughly 700 functions is covered in an article in the Power Query M function reference. These citations are created automatically by the in-product assistance.
Calculation operators can be classified as either arithmetic, comparison, text concatenation, or logical.	There are several common operators that can be used on null, including logical, number, time, date, datetime zone, duration, text, and binary.
Use DAX if you want to build a calculated column in Power BI.	On the other hand, you must utilize M language in Power Query Editor if you need to build a Custom Column.

Power Query M language specification

The group of texts that are lexically legitimate is determined by the lexical structure. The fundamental ideas of the language are values, expressions, environments and variables, identifiers, and the evaluation model. The target domain of the language is specified in depth through the precise specification of values, both primitive and structured. The types that define the basic types of values and carry additional metadata particular to the forms of structured values are themselves special sorts of values. What types of expressions are possible are determined by the set of operators in M.

A comprehensive standard library for M is built around functions, another category of special values, which also allow for the development of new abstractions. When using operators or functions during expression evaluation, mistakes can happen. Even if errors aren’t values, there are techniques to deal with them that turn errors back into values. Let expressions permit the addition of auxiliary definitions that are used to construct complex expressions incrementally. If conditional evaluation is supported by expressions. Sections offer a straightforward modularity method. (Power Query has not yet made use of sections.) The grammar bits from every other section of this document are then combined into a single, comprehensive definition in a consolidated grammar.

The essential ideas of the Power Query M language are described in the specification, including values, expressions, environments and variables, identifiers, and the evaluation model.

Creating Power Query M formula language queries.

First of all, let’s just open the Power Query Editor if your Power BI Desktop appears as shown below-

Power Bi Desktop

Go to Get Data tab or Recent sources, select the Dataset and click on “Transform Data”.

Transform data.

It will open a Power Query Window.

Power query is where the M language is written, and you can see on the right that these four stages were generated automatically. 

Applied Steps

Going forward, any modifications you make will be logged as steps. For example, if I select this column right click and remove other columns-

You see this step is generated here “remove other columns”.

I may now change the case to lowercase. I’ll click on the This Format Lowercase button after choosing the Add Column Tab.

So, when I do so, you can see that the step “put it in lowercase” is generated once more. You can see the M function written here in the Table dot add column.

You can use the Query Editor to design complex queries. A let expression encloses the variables, expressions, and values that make up a mashup query. When referencing a variable, use the # identifier and the name enclosed in quotation marks, as in #”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:

• A series of query formula steps that begin with the let statement should be created. A step variable name identifies each phase. The # character can be used as #”Step Name” to incorporate spaces in a M variable. A formula step might contain a unique formula. Keep in mind that case matters while using the Power Query Formula Language.
• 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.