Introduction to M, the Power Query Formula Language

Its name is Power Query Formula Language. But if you call it that, no one will know what you are talking about. The original informal language name M seems to have 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 gets rid of 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 as far a M is concerned. 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”}),

Betty = Table.TransformColumnTypes(Wilma,{{“Dosage”, Int64.Type}})

in

Betty

Perhaps more surprising, especially 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 is recording 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. 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”. 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. In the code above, some people might say that “in Betty” describes the goal. To achieve the goal Betty we discover that we must find first the value for Wilma. We then must keep backtracking till we have all the information we need to evaluate the functions.

The Functional Logic of an M Script

Going to the definition of Betty, we see that “Wilma” must be calculated in order to evaluate “Betty”. Wilma in turn cannot be evaluated without the value for Barney, and Barney requires Fred. It is these functional relationships that determine the sequence of code execution in M.

Unfortunately, reviewing automatically generated M scripts provides only a narrow and limited view of a very powerful formula language. The absence of intellisense in the simple editor compounds this difficulty by making it more difficult for a developer to know what options are available as he or she writes their script. At present, only by slogging through the documentation of M objects and their methods can a developer can begin to appreciate what can be accomplished in M script. In the next few blogs we shall get a feel for the depth of the M language.

Conclusion

Excel developers are most likely to have experience with imperative programming languages like VBA and C#. The fact that M is a functional language as opposed to an imperative language can make learning M a bit of a challenge. However, once the Excel user begins to appreciate the full extent of M he or she will realize the value of taking time to learn M.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.