Power Query II — M & Pipelines

Learning objectives

By the end you can:

1. Read any Power Query query as an M program: explain the let … in structure, and articulate the step-is-a-variable model in which each step is a named value computed from an earlier one.
2. Open the Advanced Editor, identify how a step references the previous one by name (including the #"Name with spaces" form), and predict what breaks when a step is renamed.
3. Explain each as sugar for (_) => …, read a bare [Column] as _[Column], and diagnose each-scope confusion in nested contexts.
4. Name and use M's three container types (the list { }, the record [ ], and the table) and access items in each with the correct bracket and index/field syntax.
5. Write and name a custom function with typed parameters ((x as number) as number => …) and invoke it over a column with Table.AddColumn(…, each f([Col])).
6. Build a folder-ingestion pipeline end to end: point at a folder, invoke a per-file transform function on every file's binary, expand the results into one table, and derive metadata (a Month label) from each file name.
7. Trap and route errors with try … otherwise, read the record try returns (HasError, Value, Error), and choose deliberately between Remove Errors, Keep Errors, and quarantining.
8. Make a pipeline robust to schema drift (a source that drops, renames, or reorders a column) using MissingField.UseNull, name-based (not position-based) references, and defensive typing.
9. Reason about performance: what query folding is, which steps preserve or break it, when to reach for Table.Buffer, and why intermediate queries should load as connection-only.

Prerequisites & connections

Builds on. EX4.01 (Power Query I. Get & Transform: connecting to CSV/Excel/folder sources, the query editor, and the common ribbon transforms, promote headers, change type, filter, split, unpivot, merge, and append). You do not need to have memorised any M from that module; you need to have seen the Applied Steps pane fill up as you clicked, because this module reveals that each of those steps was a line of the code you are about to read and write. You also lean on EX0.01's Table discipline (Ctrl+T) (every load target and many sources are Excel Tables) and on the type-awareness threaded through EX0.02 and EX1.01: M is strict about the difference between the text "1200.50" and the number 1200.5, and half of error handling is managing exactly that boundary.

Feeds forward. This is the plumbing beneath everything data-heavy that follows. EX5.01 (Power Pivot & DAX I) consumes the connection-only queries you build here as the tables of a model; a clean M pipeline is the difference between a refreshable model and a manual copy-paste ritual. On the analyst track, the folder-ingestion pattern is how you turn twelve monthly broker statements or four quarterly filings into one analysis-ready table without touching a cell, the ingestion spine under M2.05's benchmarking pulls, under any rolling KPI dashboard, and under the "refresh and it's done" property that makes a model a living system rather than a monthly chore. The try … otherwise and quarantine habits you learn here are the same data-integrity reflexes EX2.01 drills at the worksheet level, applied one layer earlier, at ingestion, where catching a bad row is cheapest. When a source system changes a column name six months from now, the robustness patterns in this module are what stand between "the refresh threw an error, investigate calmly" and "every downstream number is silently wrong."

M is just steps: let … in

First principles. Strip a Power Query query to its skeleton and you always find the same three words: let, a comma-separated list of bindings, and in. Here is a complete, minimal query that reads a worksheet Table called Sales, forces the Revenue column to a number, and keeps only the high-value rows:

`` let Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source, {{"Revenue", type number}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Revenue] > 100000) in #"Filtered Rows" ``

Read it as a paragraph. Let Source be the contents of the Sales Table; let #"Changed Type" be Source with Revenue retyped; let #"Filtered Rows" be #"Changed Type" with only the big rows kept; in the end, hand back #"Filtered Rows". That is the whole language's shape. Each name to the left of an = is a step; each step is just a variable holding a value, here, a table. The in clause names the single step whose value the query outputs (almost always the last one).