Power Query
Executive Summary
Key Takeaways
- ✓ Power Query is Microsoft's built-in data connectivity and transformation engine, used to import, clean, and reshape data before it reaches a model's calculation layer.
- ✓ A Power Query transformation is a recorded, repeatable sequence of steps, not a one-time manual edit, so the same query can be refreshed against updated source data without redoing the work.
- ✓ Because Power Query sits ahead of the calculation layer, its transformation logic is a dependency a reviewer needs to examine separately from the workbook's formulas.
Definition¶
Power Query is Microsoft's data connectivity and data preparation technology, built into Excel and several other Microsoft products, used to connect to a data source, apply a recorded sequence of transformation steps, and load the result into the workbook. The transformation logic is recorded in the Power Query M formula language and can be re-run ("refreshed") against updated source data without the underlying steps needing to be redone manually.
Why It Matters¶
In financial modelling, external data rarely arrives in the shape a model's calculation layer needs. Power Query is the layer between the raw source — an accounting export, a data room spreadsheet, a market data file — and the model's inputs, applying filtering, reshaping, and cleaning as a documented, repeatable sequence of steps rather than a one-time manual edit that has to be redone by hand every time the source data changes. See Dynamic Arrays and Power Query in Modelling for how this data-preparation layer introduces its own, less-visible dependency structure that a model reviewer needs to account for.
Continue Reading¶
Prerequisites¶
- Financial Modelling Best Practices — the parent pillar
Related Technical Guides¶
Related Glossary¶
How OXXON tests thisRun a free structural check with FMAE
Frequently Asked Questions
What is Power Query used for in Excel?
Connecting to a data source (a file, database, or web feed), applying a sequence of transformation steps to clean or reshape the data, and loading the result into the workbook — replacing manual copy-paste-and-clean data preparation with a recorded, repeatable process.
Is Power Query the same as a macro?
No. A macro (typically VBA) automates a sequence of actions performed on the workbook. Power Query is a dedicated data connectivity and transformation engine with its own interface and its own scripting language (Power Query M), purpose-built for import and reshaping rather than general workbook automation.
Does Power Query update automatically when the source data changes?
Not automatically by default — a query must be refreshed, either manually or via a configured refresh schedule, to re-run its transformation steps against the current state of the source data and update the loaded result in the workbook.
Related Articles
Dynamic Arrays and Power Query in Financial Modelling
Dynamic arrays and Power Query are the two Excel capabilities that have changed most significantly since the structural conventions in the FAST Standard and the ICAEW Financial Modelling Code were first written. Dynamic array functions such as SORT, FILTER, and UNIQUE let a single formula return and automatically resize a range of results, replacing formulas that previously had to be copied down or entered as legacy array formulas. Power Query lets a model ingest and clean external data through a recorded, repeatable transformation sequence rather than a manual copy-paste-and-clean step. Both are genuine productivity gains, and both introduce dependency structures that a conventional row-by-row, cell-by-cell review does not automatically surface — a spilled range is owned by one formula rather than many, and Power Query's transformation steps sit entirely outside the worksheet grid.
Financial Modelling Best Practices — Standards Compared
Financial modelling best practice is not a single document but a landscape of named institutional standards, each publishing its own conventions for how a model should be structured, formatted, and documented. This page defines that landscape — what a named modelling standard actually is, how the FAST Standard and the ICAEW Financial Modelling Code differ in approach and scope, and how a practitioner chooses between them or applies more than one. It sits beside, not instead of, the Knowledge Centre's structural-foundation page on what makes an Excel financial model reliable — this page is about who has codified that discipline into a named standard, and how those standards compare to one another.
Dynamic Arrays
A dynamic array formula is a single Excel formula that returns more than one result, which Excel automatically places ("spills") into the neighbouring cells below and to the right of the formula cell, without the formula needing to be copied down or across manually. Functions such as SORT, FILTER, UNIQUE, and SEQUENCE are dynamic array functions. The spill range is generated and owned by a single formula in the top-left cell; the cells it spills into are not independently editable.