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)
- Extending the Global Environment (part 25)
- 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!
Loved the simplicity and flow of writing. Thank you.
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….
This are awesome blogs! Is a hardback book available? Or an online training on M for Power Query?
No—at least, not yet. 🙂
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.
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.
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?
Thank you very much for your generosity, I was looking for this information, I feel that I will have fun
Have a nice day
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.
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….
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?
Thanks for your kind words. If there was enough interest, a book could happen. 🙂
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.
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!
Thanks for the kind words, Sergio! I am glad you have found this series helpful.
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.
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.
Hi Diego, can you post a brief code sample showing that bit of code being used?
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.