Power Query M Primer (part 1):
Introduction, Simple Expressions & let

, , ,

Sooner or later, you may find yourself working directly with the Power Query M formula language. Tools like Microsoft Excel’s Get & Transform Data and Microsoft Power BI provide point-and-click interfaces allowing you to build data transformation sequences which behind-the-scenes are implemented in Power Query M. However, these query editors can’t do everything you might need. Sometimes direct editing and authoring of M is required.

Search the Internet and you’ll find many examples showing how to use this language to solve one problem or another but little is out there describing the syntax and paradigm of the language itself. A limited knowledge of these details may be insignificant when simply copying and pasting samples and editing variable and column names. Move beyond this to weaving and writing your own solutions directly in Power Query M and a solid understanding of the language’s syntax, rules and capabilities becomes most helpful.

Grab a seat and buckle your safety harness! We’re going to barrel down the tracks, take some dives, loops and perhaps even a wiz around a few tight curves. Our adventure ride should be fast (or at least succinct, compared to studying the language specification), maybe even fun and hopefully leave you walking away with a much better understanding of the Power Query M language!

Series Index

Simplest Thing Possible

Open the advanced editor and, chances are, you’ll see a let expression. In fact, you may have seen let so often and consistently when looking at Power Query M code that you might even thing M requires it.

Nope! While let is a helpful organizer (and we’ll talk more about it in a moment), using let isn’t required. Simple expressions are just as valid.

Advanced editor window showing an expression consisting simply of the number 1

The above bit of M code contains an expression that yields a value, in this case the numeric value 1. You can’t get much simpler or unexciting than this!

let

Like our simplistic example above, let is also an expression that produces a value. However, let lets us (pun intended) define intermediate expressions whose results are assigned to variables. These intermediate expressions can then be used to produce the final value returned by the let expression.

Breaking a large expression into intermediate components with names assigned to each makes our code easier to read. Doing this also allows us to reference those intermediate components multiple times as we build toward the let expression’s final return value.

let
  Multiplicand = 10,
  Multiplier = 20
in
  Multiplicand * Multiplier

In the first part of this let expression, variables are defined by name, each followed by an equals sign then the expression producing the value to be assigned to the variable. The variable definitions are separated by commas. The in part of the expression is simply an expression that defines what let returns.

Oddballs

Since the purpose of let is to allow defining variables, it makes since that at least one of them would be used in the in expression. However, this isn’t mandatory. in can ignore all of the just-defined variables and return something else:

let
  Multiplicand = 10,
  Multiplier = 20
in
  2 + 3

Hum…why spend the effort defining variables just to let them go unused?! Thankfully, this style is rare. Just know it is possible.

Speaking of rare but possible, technically a let expression does not need to have any variable assignments. let in 2 + 3 is valid—but if all you’re really doing is writing a simple expression, why not skip the “let in” and just directly write the simple expression?

Single Variable in in

A much more common occurrence is for in to consist of a single variable (below, on the left) instead of a more complex expression, like our initial let example (repeated below, on the right).

let
  Multiplicand = 10,
  Multiplier = 20,
  Result = Multiplicand * Multiplier
in
  Result
let
  Multiplicand = 10,
  Multiplier = 20
in
 Multiplicand * Multiplier

Both produce the same result. Why do the extra work of first assigning the value to return to a variable (left) instead of simply writing its computation in the in expression (right)?

Applied Steps - Multiplicand, Multiplier, Result - with Result selectedTurns out, the “single variable in in” style helps graphical query editors. When you do point-and-click query editing, you’re probably used to seeing a list of steps that make up the data transformation you’re building. Each of these steps represents a variable assignment from the query’s let expression. The step names are literally the variables’ name.

The variable name associated with the currently-selected step is the variable name used as the sole contents of the in expression. So, on the list to the right, if we change the selected step to Multiplier, the contents of let’s in expression changes Result to Multiplier. The ability to change the current step can be useful for debugging purposes as it makes it easy for you to view the value produced by an intermediate step in your expression.

If the in expression is more complex than a single variable, query editors can’t associate it with a single step, so the “click to select step” and step list features stops working.

Nesting: Just an Expression that Produces a Value

Since a let expression is an expression that produces a value, let expressions can be used wherever values are expected. This means we can assign them to variables, nest them inside other let expressions, and use them to produce values for function call arguments.

let
  x = 20,
  y = 
    let
      a = 10,
      b = 20
    in
    a + b
in
  x * y
let
  x = 20
in
  x * (let a = 10, b = 20 in a + b)
Date.AddDays(
  DateTime.LocalNow(),
  let
    a = 2,b = 6
  in (a + b) * b
)

The left-most example shows how a let expression that’s assigned to a variable can bring organization to a complex formula (at least, if you can imagine that this example is a complex formula!). The other two are technically valid and helpful in showing what’s possible with nesting; however, from the developer-readability standpoint, both would probably be better served if the nested expression were assigned to a variable and then that variable was referenced where the nested expression is currently used.

Next Time

Coming up next: Functions: Defining!

Leave a Reply

Your email address will not be published. Required fields are marked *