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!
- Introduction, Simple Expressions &
let(part 1) [this post]
- Functions: Defining (part 2)
- Functions: Function Values, Passing, Returning, Defining Inline, Recursion (part 3)
- Variables & Identifiers (part 4)
- Paradigm (part 5)
- Types—Intro & Text (Strings) (part 6)
- Types—Numbers (part 7)
- Types—The Temporal Family (part 8)
- Types—Logical, Null, Binary (part 9)
- Types—List, Record (part 10)
- Tables—Syntax (part 11)
- Tables—Table Think I (part 12)
- Tables—Table Think II (part 13)
- Control Structure (part 14)
- Error Handling (part 15)
- Type System I – Basics (part 16)
- Type System II – Facets (part 17)
- Type System III – Custom Types (part 18)
- Type System IV – Ascription, Conformance and Equality’s “Strange” Behaviors (part 19)
- Metadata (part 20)
- Identifier Scope & Sections (part 21)
- Identifier Scope II – Controlling the Global Environment, Closures (part 22)
- Query Folding I (part 23)
- Query Folding II (part 24)
- More to come!
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.
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.
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!
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
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
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
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)?
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
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.
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
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.
Coming up next: Functions: Defining!