The Advanced Editor
Arjun's Sales query has grown to a dozen steps, and a new colleague asks what it actually does. Clicking each step and reading the formula bar one at a time is slow. Home → Advanced Editor shows the whole recipe at once, written as a single piece of M. Reading it is the first step towards writing M yourself.
The rule
letlists named steps, each using an earlier one;innames the result.
Here is a four-step version of the Pune query, reading a local copy of the file, as the Advanced Editor shows it:
let
Source = Csv.Document(File.Contents("C:\LeafAndCup\pune-2026-01.csv"), [Delimiter = ",", Encoding = 65001]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"OrderID", Int64.Type}, {"Qty", Int64.Type}, {"Price", type number}}),
#"Removed Test Rows" = Table.SelectRows(#"Changed Type", each [Store] <> "TEST")
in
#"Removed Test Rows"How to read it:
- Every line between
letandinhas the shapeName = expression. The names are exactly the names in Applied Steps. - Steps are separated by commas. The last step before
inhas no comma after it. - A simple name such as
Sourceis written plainly. A name with spaces or symbols is written as#"Like This". - Most steps take the previous step's name as their first argument. That is how the chain is built.
inis followed by one step name, and that step's value is the query's output. Normally it is the last step.- M is case-sensitive:
Table.SelectRowsworks,table.selectrowsdoes not. each [Store] <> "TEST"is a small function run once per row;[Store]means this row's Store value.//starts a comment that runs to the end of the line.
The shortcut
Read M from the bottom up. Start at in, find that step, then follow the name it uses as its input, and keep going until you reach Source. That chain is what the query really does.
Start at in
The query returns
#"Removed Test Rows".
Two traps the shortcut catches. Delete a comma between steps and the editor reports a syntax error, because it expected a comma. Add a new step at the bottom but leave in pointing at the old last step, and your new step is simply ignored: the query still returns whatever in names.
Drills
Answer fast — trust the rule.
What does the Pune query above return?
Why is #"Changed Type" written with # and quotation marks?
Arjun writes a new step using table.selectrows(...). What happens?
Arjun adds a comma and a new last step, Sorted = Table.Sort(#"Removed Test Rows", {{"Qty", Order.Descending}}), but leaves in #"Removed Test Rows". What does the query output?
In each [Store] <> "TEST", what does [Store] mean?
Remember
letlists named steps, each using an earlier one;innames the result.- Read from the bottom up: start at
inand follow each step's input back to Source. - Traps: a missing comma, and a new step that
innever reaches.