Why Every Financial Modeller Should Learn Power Query

I watch financial modellers waste hours every month doing the same thing: copying data from a source system, pasting it into Excel, cleaning up the headers, removing blank rows, reformatting dates, pivoting the layout, and feeding the result into their model. Then next month, they do it again. And again. And again.

Power Query does all of this in a single click. It’s built into Excel. It’s free. And most financial modellers have never opened it.

This needs to change.

What Power Query Actually Is

Power Query is an ETL (Extract, Transform, Load) engine embedded in Excel and Power BI. It connects to data sources — CSV files, databases, web APIs, other Excel workbooks, SharePoint, PDF tables, JSON feeds — and transforms the data through a series of steps that are recorded and repeatable.

The key word is repeatable. When your data source updates, you click “Refresh All” and every transformation runs again automatically. The same cleaning, reshaping, and formatting that took you 45 minutes manually happens in under a second.

Why Financial Modellers Need It

The Monthly Reporting Problem

Every FP&A team I’ve worked with has the same workflow: download the trial balance from the ERP, paste it into a workbook, manually map accounts to model categories, build a summary, and feed it into the forecast model.

With Power Query, the entire pipeline is automated. The query connects to the ERP export (or a shared folder where the export lands), applies the account mapping, summarises by category, and loads the result directly into the model’s actuals sheet. The monthly reporting cycle drops from half a day to five minutes.

The Data Cleaning Problem

Financial data is rarely clean. Source systems export dates in inconsistent formats, include subtotal rows that break your formulas, mix text and numbers in the same column, use merged cells, or have trailing whitespace that makes VLOOKUP fail silently.

Power Query handles all of this declaratively. You define the cleaning steps once — remove rows where Column A is blank, change data types, trim whitespace, unpivot from wide to long format — and they apply every time. No more “I forgot to delete the subtotal rows” errors.

The Multi-Source Problem

A financial model often needs data from multiple sources: actuals from the ERP, headcount from the HR system, capex schedules from project management, market data from an external provider. In a traditional workflow, someone manually collates these into a single workbook.

Power Query can connect to all sources simultaneously, merge and append the data, and load a unified dataset into the model. When any source updates, the model refreshes with one click.

A Practical Example: Trial Balance to Model Input

Let’s say your ERP exports a trial balance as a CSV with columns: Account Code, Account Name, Period, Debit, Credit.

Your model needs: a summary by P&L category, by month, with net balances (debits minus credits), for the last 12 months only.

In Power Query, the steps are:

1. Connect to the CSV file
2. Filter Period to last 12 months
3. Add a column: Net Balance = Debit - Credit
4. Merge with a mapping table that assigns each Account Code to a model category (Revenue, COGS, Overheads, etc.)
5. Group by Category and Period, summing Net Balance
6. Pivot Period into columns (so each month is a column)
7. Load into the model’s actuals sheet

Once built, this query refreshes in seconds. When next month’s CSV arrives, you replace the file and click Refresh. Done.

The M Language Is Not as Scary as It Looks

Power Query has a point-and-click interface that generates M code (the underlying functional language) automatically. For 80% of financial modelling use cases, you never need to write M code directly.

For the other 20% — custom transformations, dynamic date ranges, parameterised queries — M is approachable if you’ve ever written Excel formulas. It’s a functional language, which means every expression returns a value and transformations chain naturally.

A common example: filtering a table to the last 12 complete months.

let
    Source = Csv.Document(File.Contents("C:\Data\TrialBalance.csv")),
    FilteredRows = Table.SelectRows(Source, each [Period] >= Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -12)),
    // ... further transformations
in
    FilteredRows

The syntax is readable, the logic is linear, and the query editor shows you the result of each step interactively.

Power Query vs VBA for Data Processing

“I already do this with a VBA macro.” I hear this often. And yes, VBA can do everything Power Query can do for data transformation.

But Power Query has three advantages over VBA for this type of work:

Transparency. A Power Query query is a series of named, visible steps in the query editor. Anyone can open it, see what each step does, and modify it. A VBA macro is code hidden behind a button — the next person who inherits the model may not be comfortable reading or modifying it.

Maintainability. When the data source changes — a column gets renamed, a new field appears — modifying a Power Query step is trivial. Modifying a VBA macro that parses specific column positions is fragile and error-prone.

No code execution risk. Power Query runs in a sandboxed environment. VBA macros execute arbitrary code, which means corporate IT departments often disable them, and cautious recipients won’t enable macros on an unfamiliar workbook. Power Query works without macros enabled.

For data transformation, use Power Query. For automation of Excel-native tasks (formatting, printing, interacting with the workbook object model), use VBA. They complement each other; they don’t compete.

Getting Started

If you’ve never used Power Query, here’s where to start:

1. Open Excel, go to the Data tab, and look for Get Data (or Get & Transform). It’s been there since Excel 2016.
2. Connect to a CSV or Excel file you use regularly — your monthly management accounts export, for example.
3. Use the query editor to apply transformations: remove blank rows, change data types, rename columns.
4. Load the result into a worksheet and observe that you now have a refreshable data pipeline.
5. Next month, replace the source file and click Refresh All. Watch the magic happen.

The learning curve is gentle. Within a few hours, you’ll be able to automate the data preparation work that currently takes you half a day. Within a few weeks, you’ll wonder how you ever tolerated doing it manually.

The Bottom Line

Power Query is the single most impactful tool a financial modeller can add to their toolkit. It eliminates the manual, error-prone, time-consuming work of getting data into your model — the work that isn’t modelling but currently eats a disproportionate share of your time.

It’s free. It’s built into Excel. It’s been there for years. If you’re not using it, start today.

Will Wardle uses Power Query extensively in his financial modelling practice to automate data preparation, ETL pipelines, and model refresh workflows. He trains finance teams in Power Query as part of his modelling training programmes.