Introduction to M, the Power Query Formula Language

2023-03-31

Its name is Power Query Formula Language. But no one will know what you are talking about if you call it that. The original informal language name M seems stuck, and even the Microsoft documentation refers to M.

Every time you create a query in Power Query, whether in Excel or Power BI, Power Query generates a functional script in M. Most people accomplish everything they need using the graphical environment and never have to confront the M language. Ironically, as different as M and Excel VBA are in terms of computer languages, they both accomplish the same thing; they provide a means of achieving new functionality when the capabilities of the graphical tools have been pushed to their limit.

The Fundamental (and Functional) Structure of an M Query

Let's reexamine some M code generated automatically in a previous blog. This code removes the "mg" label in a column named "dosage" and then changes the column datatype.

let
Source = Excel.CurrentWorkbook(){[Name="DrugDataUnpivot"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Drug", type text}, {"Dosage", type text}, {"Value", Int64.Type}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type," "mg,", Replacer.ReplaceText,{"Dosage"}),
<#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Dosage", Int64.Type}})
in
#"Changed Type1"

Somewhat ironically, one of the syntactical features of the M language that most obviously stands out as being different is, in fact, trivial and of no consequence. Names like #"Changed Type1" could be replaced with ChangedType1 if you like. The leading # and the double quotes are only there because of the space in the name. The value names like Source and #"Replaced Value" have mnemonic value but have no meaning regarding M. The code could just as well read.

let
Fred = Excel.CurrentWorkbook(){[Name="DrugDataUnpivot"]}[Content],
Barney = Table.TransformColumnTypes(Fred,{{"Drug", type text}, {"Dosage", type text}, {"Value", Int64.Type}}),
Wilma = Table.ReplaceValue(Barney,"mg","",Replacer.ReplaceText,{"Dosage"}),
in
betty =="" table.transformcolumntypes(wilma,{{"dosage",="" int64.type}})
Betty

Perhaps more surprising, primarily if you have not worked with functional programming languages before, is that the four steps in the query are not defined by their sequence in the M code. Power Query displays the steps in the natural order since it records steps as they are defined in the graphical environment. But if the order of the steps in the M code were juggled, it would make no difference to the query's function. For example, the following code is identical in function to that above:

let
Betty = Table.TransformColumnTypes(Wilma,{{"Dosage", Int64.Type}}),
Barney = Table.TransformColumnTypes(Fred,{{"Drug", type text}, {"Dosage", type text}, {"Value", Int64.Type}}),
Wilma = Table.ReplaceValue(Barney,"mg","",Replacer.ReplaceText,{"Dosage"}),
Fred = Excel.CurrentWorkbook(){[Name="DrugDataUnpivot"]}[Content]
in
Betty

Note that M demands each line of code end with a comma, except the last line before the "in." As a result, VBA programmers may be a little frustrated when they discover that M is case-sensitive.

The sequence of code execution is determined by the function references, not by the code sequence in the script. For example, in the code above, some might say that "in Betty" describes the goal. To achieve the goal, Betty, we discover that we must first find value for Wilma. We then must keep backtracking until we have all the necessary information to evaluate the functions.

Going to the definition of Betty, we see that "Wilma" must be calculated to evaluate "Betty." Furthermore, Wilma cannot be evaluated without the value for Barney, and Barney requires Fred. These functional relationships determine the sequence of code execution in M.

Unfortunately, reviewing automatically generated M scripts provides only a narrow and limited view of a compelling formula language. The absence of intellisense in the simple editor compounds this difficulty by making it more difficult for developers to know what options are available as they write their scripts. However, a developer can begin to appreciate what can be accomplished in M script by slogging through the documentation of M objects and their methods.

Conclusion

Excel developers will likely have experience with imperative programming languages like VBA and C#. The fact that M is a functional language instead of an imperative language can make learning M challenging. However, once the Excel user begins to appreciate M's full extent, they will realize the value of taking time to learn M.

Take your Power BI training to the next level with Training from Learning Tree.

This piece was originally posted on January 18, 2018, and has been refreshed with updated styling.

Written by Dan Buskirk

"The pleasures of the table belong to all ages." Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.