The each keyword in Power Query

Two small pieces of grammar unlock most of M's power: the keyword each, and the three shapes data comes in.

Start with each and _. Look again at Table.SelectRows(#"Changed Type", each [Revenue] > 100000). Table.SelectRows needs a function that takes one row and returns true/false. Written out in full, that function is (row) => row[Revenue] > 100000. M gives you a shorthand. each means (_) => …, where _ is the single argument, named for you automatically. Inside an each, a bare [Revenue] is shorthand for _[Revenue]. So these three lines are identical:

Table.SelectRows(Source, each [Revenue] > 100000)
Table.SelectRows(Source, (_) => _[Revenue] > 100000)
Table.SelectRows(Source, (row) => row[Revenue] > 100000)

In other words, each is a one-argument lambda, and the surrounding function applies it once per row. There's no magic in it, and it isn't a loop. Knowing what it expands to lets you handle the cases each can't express cleanly. One is a two-argument function. Another is a nested context, where you have to name the outer row yourself to avoid the _ collision you'll meet among the common mistakes.

What are the three containers in M?

Now the three containers. Every value in M that isn't a scalar is one of three shapes, and they use different brackets:

{ 10, 20, 30 }                                  a LIST     — ordered, curly braces { }
[ SKU = "A-1", Qty = 5, Rev = 1200 ]            a RECORD   — named fields, square brackets [ ]
#table({"SKU","Qty"}, {{"A-1", 5}, {"B-2", 9}}) a TABLE     — rows and columns

• A list is an ordered sequence you index by position, zero-based, with braces. Given L = {10, 20, 30}, L{0} is 10 and L{2} is 30. A column pulled out of a table is a list, so Source[Revenue] is the list of all revenue values. You feed that list straight to List.Sum.
• A record is a set of named fields you access by field name, with square brackets. Given R = [SKU="A-1", Qty=5], R[SKU] is "A-1". A single row of a table is a record, so _[Revenue] inside an each reads a field of the current row.
• A table is the workhorse of rows and columns. Under the hood it's a list of records. That matters more than it sounds. It's why _[Column] works on a row, since that's record access. It's also why Table.Column returns a list.

When do you use braces and when square brackets?

Keep the brackets straight. Braces { } are for lists and positional access, and square brackets [ ] are for records and field access. Mix them up, writing L[0] or R{SKU}, and you get an immediate error. It's one of the most common beginner stumbles. You'll see all three containers constantly. Excel.CurrentWorkbook(){[Name="Sales"]} in the first example is list access by position ({…}) using a record as the search key ([Name="Sales"]). Read it as "from the list of workbook items, the one whose Name field is Sales."