The first Power Query lesson sold the idea: transformations recorded once, replayed on refresh. This one is about the three transformations worth the ticket price on their own — the moves that are painful or impossible in the grid, and one-click steps in the editor.
Unpivot: the report that should have been data
The most valuable button in the whole editor. Data constantly arrives cross-tabbed — products down the side, months across the top — because that’s how humans like to read it. But everything you’ve built wants it long: one row per product-month, with a Month column. Cross-tab in, analysis blocked: you can’t SUMIFS by month when month is twelve column headers, and January’s arrival as a new column breaks every formula pointed at the old shape.
In the editor: select the identifier columns (Product), right-click → Unpivot Other Columns. The twelve month columns collapse into two — Attribute (the month) and Value — and the cross-tab becomes proper data. “Other columns” is the phrasing that future-proofs it: when the next file carries a thirteenth month, it unpivots too, because you never named the months. A whole genre of copy-paste-transpose misery, deleted.
(The pivot’s own job — going back from long to readable-wide — already belongs to the PivotTable. The two tools are a matched pair: store long, present wide.)
Merge: the join, done upstream
Merge Queries is the lookup performed at the recipe layer: match Orders to Customers on the key, expand the columns you want, done — before the data ever reaches a sheet. When to prefer it over in-sheet lookups: when the join is structural (every refresh, forever) rather than exploratory; when there are hundreds of thousands of rows of lookup formulas to avoid; and when you need join kinds — left, inner, full outer, or left anti (“rows with no match”), which turns the comparing-two-lists routine into a refreshable query that labels new/gone/changed by itself. This is also where fuzzy matching properly lives — thresholds, transformation tables, repeatable.
Append: the stack
Append Queries stacks tables with matching columns — this year’s file atop last year’s, the folder-combine’s manual cousin for sources that live in different places or shapes. The one discipline: normalise the columns first (names and types must agree; the editor’s rename/retype steps are exactly for this), and add a Source column so the audit trail survives the stacking.
The senior habit
The trio shares one mental model worth naming: shape at the recipe layer, think at the sheet layer. Unpivot fixes shape, merge fixes relationships, append fixes fragmentation — all before a single cell exists, all replayed per refresh, all documented in Applied Steps that read like a checklist. The workbooks that stay fast, small and auditable are the ones whose sheets receive finished data — and these three buttons are how data gets finished.
Unpivot when headers hold facts. Merge when tables belong together. Append when one table arrived in pieces. Three moves, learned once — recorded forever.