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 sense 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 selected

Turns 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!

Related posts:

  1. Power Query M Primer (Part 4): Variables & Identifiers
  2. Power Query M Primer (part 3):
    Functions: Function Values, Passing, Returning, Defining Inline, Recursion
  3. Power Query M Primer (part 2):
    Functions: Defining
  4. Power Query M Primer (Part 5): Paradigm

19 thoughts on “Power Query M Primer (part 1):
Introduction, Simple Expressions & let”

  2. Siraj Samsudeen

    Hi Ben, what a wonderful series – I fully identify with your observation ” little is out there describing the syntax and paradigm of the language itself.” I was looking for a way to get deep into M and understand it – I have thoroughly enjoyed reading this first part. Finally let is starting to make sense to me….

    Reply ↓
  4. Gene Cloud

    Learned a lot from these posts. Still not sure why I would nest Let-In’s.
    I’ve been working with Excel Queries since 2018. Had a number of issues with hangups and crashes. My approach is simplistic and possibly naive. I do simple short queries that reference and build until I get an answer.
    I think if I load a query to a table, then build a new query from that result table, I can avoid rerunning the first query before the second can run, i.e. the table is static. Is that true? Is there a better way ? With truly large data sets we can load everything into a table.
    Sometimes, I am forced to write a query to correct an error that the source owner chooses not to fix. I guess this is one of those cases where I should use a different tool to fix the error permanently. Again – Thanks for the hard work.

    Reply ↓
  5. Not Power Query

    Hi Ben,
    Thank you a lot for this tutorial series!

    I’m wondering why there’s difference in these expressions
    1. “let hello = if true then 10 else 5 in hello” is OK.
    2. “hello = if true then 10 else 5” is NOT OK.

    Reply ↓
    1. Ben Gribaudo Post author

      Thanks for your kind words!

      In Power Query, every statement must return a value or raise an error. The fundamental problem with the second example is that it doesn’t return anything; rather, it simply assigns a value to a variable.

      Does that help?

      Reply ↓
  6. Michel

    Thank you very much for your generosity, I was looking for this information, I feel that I will have fun
    Have a nice day
    Michel Tremblay

    Reply ↓
  7. Celia Alves

    A friend just recommended me this series. I just read this first topic and I already took so much value! WOW! Thank you, Ben! I am looking forward to continuing through the other posts.

    Reply ↓
  8. Ivan

    Hi Ben
    Your series has helped me more than all the books I’ve read, or started reading, so far.
    Excellent work – very well explained and easy to follow….
    Thank you

    Reply ↓
  9. holofanboi

    Came across this treasure thanks to a kind reddit post. Can’t believe theres finally a source to ‘study’ M. Agree wholeheartedly that there is barely any available centralized material online in explaining the syntax of M.

    Any chance that you will publish somesort of Definitive Guide to M?

    Reply ↓
      1. Rob

        I think there is a big big market for an M-code book, or even better a video course that runs power query through M-code only, as if the buttons never existed.

        Reply ↓
  10. Sergio Castro-Ramiro

    Thanks for this series! Yes, I found really hard to find resources to learn M, and I’ve ended up at a stage where I need to modify formulas and create functions. Thanks Ben!

    Reply ↓
  11. Steve Ross

    Very helpful post. I experimented a bit with the query below, which illustrates nested let statements. I am posting in the event anyone finds it useful. It assigns a value to a variable named var0, then uses let statements to determine values for var1 and var2. The two inner let statement uses intermediate variables x, y and z. 

    let
// assign the value 100 to var0
  var0 = 100, 
// let statement returns a value assigned to a variable name var1, in this case 3
  var1 = 
     let
        x = 1,
        y = 2,
        z = x + y
     in
     z,   
// let statement returns a value assigned to a variable name var2 , in this case 5
  var2 = 
     let
        x = 2,
        y = 3,
        z = x + y
     in
        z,
// variable result is the product of the values returned from the two let statements and var0      
result = var1 * var2 * var0       //  3 * 5 * 100 = 1500
in
// result is the value returned from the overall expression
result
    Reply ↓
  12. Diego

    Hi Ben, are you aware if there is some restriction of using variables in M language over a Power Bi Service such as Dataflows? I do have a dataFlow and want to add some variables to control the dates range of the data but as soon as I add a simple line (Multiplicand = 10,) I’m getting the “Encrypting the connection isn’t supported for Data source kind: Web” error message. Again, only shows when adding that line.

    Reply ↓
  13. David McKinney

    This is a great series that I’ve just discovered. Like landing on a rich seam of gold! I’m going to enjoy working through these posts, and look forward to the insights and openings it will provide.

    Reply ↓

Leave a Reply

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