Power Query for Beginners: Stop Cleaning the Same Report Twice

Guide · Updated July 2026 · ~8 min read

If you have ever pasted a bank export into Excel, deleted the same three junk columns, split the same date field, and then done all of it again next month, Power Query is the tool you have been missing. It records your cleanup once and replays it on new data with a single click. This guide walks through what Power Query is, the Get & Transform workflow, the handful of transforms you will use constantly, and a real finance example you can copy.

What Power Query actually does

Power Query is Excel's built-in data cleanup engine. It lives on the Data tab under Get & Transform Data, and it has been baked into every Excel version since 2016 (no add-in to install on Windows). You point it at a source — a table, a CSV, a folder of files, a database, a web page — and then you shape the data step by step in a separate window called the Power Query Editor.

The key idea: Power Query does not change your source, and it does not do the work only once. Every action you take is recorded as a step. When next month's file arrives with the same messy layout, you drop it in and hit Refresh, and the whole sequence runs again in seconds. You are building a reusable pipeline, not doing a one-off scrub.

Why it beats manual copy-paste

Manual cleanup feels fast the first time and punishes you every time after. Here is the honest comparison for anyone doing recurring reporting:

• It is repeatable. Your steps are saved. A five-minute monthly reconciliation prep becomes a one-click refresh.
• It is auditable. The Applied Steps list on the right is a plain-English record of exactly what happened to the data — "Removed Columns," "Changed Type," "Filtered Rows." Compare that to a formula-riddled sheet nobody can reverse-engineer.
• It does not break formulas. Because the transform lives outside the worksheet, you are not dragging fragile VLOOKUPs down 40,000 rows.
• It handles volume. Power Query happily chews through files far larger than you would want to sort and filter by hand.
• It combines sources. Stacking twelve monthly branch files, or joining a customer list to an invoice list, is a two-minute job instead of an afternoon.

For finance teams, that last point is the quiet killer feature. Consolidating branch or entity exports is exactly the kind of work that eats hours and invites copy-paste errors.

How to use Power Query: the Get & Transform workflow

Every Power Query job follows the same three-stage rhythm: connect, transform, load.

1. Connect. On the Data tab, open the Get Data dropdown and pick your source — From File > From Workbook / Text/CSV, From File > From Folder to combine many files, From Database, or From Web. To clean data that is already in your sheet, select any cell in it and click From Table/Range (labelled From Sheet in some Microsoft 365 builds). If it is not already a table, Excel offers to convert it.
2. Transform. The Power Query Editor opens in its own window. You reshape the data here using the ribbon (Home and Transform tabs) and right-click menus. Every click adds a step to Applied Steps on the right, which you can rename, reorder, or delete with the little X.
3. Load. On the editor's Home tab, click Close & Load to drop the result onto a new worksheet as a table. Prefer Close & Load To… when you want control — load as a Connection only (no worksheet), send it straight to a PivotTable, or pick a destination cell.

That is the loop. Ninety percent of Power Query work is just those three stages repeated.

A first real example: consolidating monthly sales

Say you receive a sales file each month with columns like Region, then one column per product — Software, Hardware, Services — and the amounts sitting under each. That wide layout is readable but useless for a PivotTable. Here is the fix, once:

1. Click inside the data and choose Data > From Table/Range. The editor opens.
2. If the source has junk columns you do not need, select them (Ctrl-click the headers) and press Remove Columns on the Home tab. Keep every column you still need for the next steps — here that is Region plus the Software, Hardware and Services figures we are about to unpivot.
3. Select the three product columns (Software, Hardware, Services). On the Transform tab, click Unpivot Columns. Power Query collapses them into two tidy columns: Attribute and Value.
4. Double-click the Attribute header and rename it Product; rename Value to Amount.
5. Click the small type icon on the Amount column and confirm it is a Decimal Number, and set Product to Text.
6. Click Close & Load. You now have a clean, PivotTable-ready table.

Next month, save the new file over the old source (or into the watched folder), hit refresh, and the entire sequence reruns. No re-clicking.

Skip the editor: let HISAB 360 write the M

Every step in the walkthrough above is recorded in a language called M — the code Power Query generates behind the Applied Steps. You never have to look at it, but writing M by hand is a real skill, and some days you just want the finished query. This is where an AI layer earns its keep.

HISAB 360 is an AI assistant that runs inside Excel. You describe the transform in plain English and it writes the M, creates the query, and loads the result as a normal table you can refresh like any other. Suppose you have a folder of bank exports and you type:

"Combine every CSV in this folder, keep only Date, Vendor, and Amount, and remove the blank rows."

HISAB writes something like this and runs it for you:

let
    Source = Folder.Files("C:\Finance\Bank Exports"),
    OnlyCsv = Table.SelectRows(Source, each [Extension] = ".csv"),
    Combined = Table.Combine(
        List.Transform(OnlyCsv[Content],
            each Csv.Document(_, [Delimiter = ",", Encoding = 65001]))),
    Headers = Table.PromoteHeaders(Combined, [PromoteAllScalars = true]),
    KeptCols = Table.SelectColumns(Headers, {"Date", "Vendor", "Amount"}),
    NoBlanks = Table.SelectRows(KeptCols, each [Date] <> null and [Amount] <> null),
    Typed = Table.TransformColumnTypes(NoBlanks,
        {{"Date", type date}, {"Vendor", type text}, {"Amount", type number}})
in
    Typed

The output lands on a new sheet as a table and appears in Queries & Connections exactly like a query you built by hand. Drop next month's CSVs into the folder, hit Data > Refresh All, and it reruns — the same one-click payoff as the manual route, because it is an ordinary Power Query under the hood. Open the Power Query Editor any time to read the Applied Steps and tweak them.

Treat this as a shortcut, not a substitute for understanding the tool. The manual workflow above is worth learning — the better you know what Unpivot, Merge, and Changed Type actually do, the sharper your plain-English requests become, whether you type them into the editor yourself or hand them to HISAB.

Common transforms you will use constantly

You do not need to learn all 300-odd options. These four cover most day-to-day work:

• Unpivot turns a wide, one-column-per-period layout into a tall, analysis-friendly one. It is the single most useful transform for finance data and the hardest to do by hand. If you want to keep a couple of ID columns fixed, select those, then choose Unpivot Other Columns.
• Remove Columns strips out the fields you will never report on. Select the columns and use Home > Remove Columns, or right-click a header. Removing other columns is often safer — it survives new stray columns appearing in the source.
• Combine stacks or joins data. Append Queries (Home > Combine) piles rows from multiple tables on top of each other — perfect for twelve monthly files with identical columns. Merge Queries joins two tables on a shared key, the way a VLOOKUP would, but without a single formula.
• Filter and Changed Type quietly do a lot: click a column's dropdown arrow to filter out blanks or a status you do not want, and always confirm each column's data type so dates sort as dates and amounts sum as numbers.

Refreshing: where the payoff lands

Once a query is loaded, refreshing is the whole point. Go to Data > Refresh All to update every query in the workbook, or right-click the loaded table (or the entry in the Queries & Connections pane) and choose Refresh to update just one. If your source is a folder, dropping a new file in and refreshing is all it takes to fold it into the report.

A practical tip: open Data > Queries & Connections > (right-click a query) > Properties and tick Refresh data when opening the file for reports that always need to be current when someone opens them.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.