Yaa reconciles depot readings for a fuel distributor in Sekondi. Every Monday she opened four CSV exports, deleted the top three rows of each, split a combined column, fixed the dates, joined them and pasted the result: about forty minutes, done identically, forever.
Power Query does that once and replays it. Not faster by hand, but recorded, so the second Monday is one button and the fortieth Monday is the same button.
What it actually stores
Not the data. A list of steps, each one a transformation, in order, that runs against whatever the source contains today.
Four depots, every Monday
Six recorded steps, and the two that break when a file changes shape
The list on the right of the editor is the query. Clicking any station shows the table as it was at that point, which is what makes a Monday failure locatable in a minute.
- 01SourceA folder rather than a file, so a fifth depot is a file drop. Holds a path, and a moved folder breaks it
- 02Removed the top three rowsThe title block the export system prints above the headings
- 03Promoted headersRow one becomes column names, which every later step refers to by name
- 04Changed typeAdded automatically, pinned to the first rows, and the commonest recurring failure
- 05Split the depot codeOne column into two, on the delimiter
- 06Merged the price tableA lookup once for the whole table, and it does not slow at 400,000 rows
The stations are named as intentions rather than operations: renamed steps make a Monday failure clear eight months later.
That is the whole idea and it is the part people miss when they first meet it. A pivot table summarises data that is already in the workbook; a query describes how to get data in and what to do to it on the way, and it holds no numbers of its own.
The steps appear as a numbered list on the right of the editor: Source, Promoted Headers, Changed Type, Split Column, Removed Columns. Each one can be clicked to see the table as it was at that moment, which makes a failure locatable rather than mysterious.
The three things it does that Excel cannot
Read a whole folder. Point a query at a folder rather than a file and it reads every file in it and stacks them. Yaa's four depots became one query, and a fifth depot is a file dropped into a folder rather than a formula rewritten.
Core engine advantages
Three transformations Power Query executes that standard Excel cannot
Power Query handles multi-file stacking, table unpivoting, and bulk joining without workbook formula bloat.
- Folder consolidationPoints to an entire directory and stacks four depot files into one unified table
- Dynamic unpivotTransforms wide horizontal month columns into normalized two-column key-value pairs
- Bulk relational mergeJoins two tables on a key once, remaining fast at 400,000 rows where lookups stall
Traditional Excel limitations
Merging tables once in Power Query eliminates thousands of recalculating lookup formulas.
Unpivot. A sheet with months across the top is readable and useless for analysis; select the month columns, choose Unpivot, and twelve columns become two, which is the shape a pivot table needs. This one transformation is the reason most people eventually learn the tool.
Merge two tables on a key, which is a lookup done once for a whole table rather than a formula in every row, and it does not slow down at 400,000 rows the way 400,000 lookups do.
Where a query breaks
A renamed column. Steps refer to columns by name, so a source that renames Volume to Volume (L) breaks every step after the rename, with an error naming the column. It is an easy repair and it is the commonest one.
Pipeline failure prevention
Three fragile query assumptions and how to safeguard them
Queries rarely break due to logic errors; they fail when upstream source files alter column headers, types, or paths.
- Column naming+Renaming Volume to Volume (L) in source data breaking downstream steps→Stable header contracts and explicit rename repairs in the formula bar
- Type coercion+Automatic early Changed Type step throwing errors on unexpected text formats→Deleting premature type steps and enforcing types only at the final query stage
- File storage paths+Source step breaking when files move into subfolders or renamed network shares→Editing the Source path under Queries and Connections to point to stable shares
Deferring Changed Type to the very end of the query prevents valid edge-case data from triggering errors.
Changed Type, applied too early. Power Query adds this step automatically as soon as you load a table, pinning each column to the type it saw in the first rows. When next month's file has a value that does not fit, the step errors on data that is perfectly fine. Deleting that automatic step, or moving it to the end, prevents most recurring failures.
A moved file. The Source step holds a path, and a file moved into a subfolder or a renamed shared drive breaks it. Data, then Queries and Connections, then edit the source, is the repair.
The habit that makes a query maintainable
Rename the steps. Each one can be given a name, so a list reading Removed the header rows, Split the depot code, Joined the price table is a document. The default names describe the operation and not the intent, and the intent is what you need in eight months.
Maintainable query design
Monolithic recorded steps versus staged intent-based queries
Renaming mechanical steps and separating raw ingestion from transformations keeps queries maintainable over time.
Separating raw loading from downstream transformations ensures source path changes only require editing one query.
And keep the raw load separate from the transformations where a query is shared: one query that only loads, and another that references it and transforms, so a source change is fixed in one place.
When not to use it
For a one-off. Recording steps for something done once costs more than doing it once, and the whole value is in the replay.
Effort versus replay value
One-off manual cleaning versus recorded Power Query pipelines
Recording steps for a task done only once wastes time; the value of Power Query emerges entirely on the second run.
- One-Off Ad-Hoc TaskClean directly by hand
- Task will never be repeated on future data exports
- Setup overhead of opening query editor exceeds task duration
- Zero return on investment from recording steps
- Recurring WorkflowRecord in Power Query
- Process will run a second time on next Monday's file drop
- Forty minutes of weekly manual cleanup compressed into one button
- Identical transformations replayed with zero human error
Automation Threshold
The moment a task must be done a second time is the exact threshold to record it in Power Query.
The test is the second time. If a task is about to be done a second time in the same way, that is the moment to record it, which is the same trigger that decides when to write down a process.
What to use, as of 2026
Power Query, built into Excel on the Data ribbon as Get and Transform since Excel 2016, and available in 2010 and 2013 as a free add-in. In 2026 it is present in every current version and it replaced the weekly forty minutes of identical manual work.
Technology evolution
Power Query from optional add-in to core Excel automation
Power Query replaced weekly manual copy-pasting with a single Refresh All button across all current Excel releases.
- 2010Free add-in launchReleased as an optional download for Excel 2010 and 2013
- 2016Native Data ribbonIntegrated directly into core Excel as Get and Transform
- FolderFrom Folder importReplaced opening four depot exports manually with directory ingestion
- 2026Universal replay standardRefresh All button provides identical weekly automated execution
In 2026 Power Query is built into every current Excel version, eliminating forty minutes of recurring manual work.
The Refresh All button, which is the entire point: the same steps against this week's files.
Get Data, then From Folder, for the multi-file case, which replaced opening four exports and pasting them under each other.
A model will explain what an existing query step does and suggest how to restructure one, free on every major assistant, and it is helpful on the M language that sits underneath. It cannot see next month's file, and the failures on this page are all caused by a file that changed shape rather than by a query that was written wrongly.
The transformations being recorded are the same ones done by hand in text, dates and the imported mess, and the merge step replaces a column of VLOOKUP, XLOOKUP and INDEX MATCH. What the loaded table is usually built into next is summarising thousands of rows in seconds, under the most used analysis tool in the world.