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.


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}})


#"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


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}})



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:


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]



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.

M Query example

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.


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.

Dan Buskirk

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.

Chat With Us