How to automate a monthly reconciliation in Excel with Power Query

Why the copy and paste version keeps breaking

Most monthly reconciliations start the same way. Someone exports receivables, cash receipts and a ledger report, pastes them into a workbook, fixes the dates, trims the columns and starts matching with lookups and filters. The logic lives in the order of those steps and in the head of the person doing them.

That is where the errors come from. A row gets pasted twice, a date imports as text, a filter is left on from last month. None of these are hard problems. They are repetition problems, and Power Query is built for repetition.

Step 1: Put every export in one folder

Create a folder per source, for example AR, Receipts and GL, and save each month's export there with the period in the file name. Keep the exports untouched. The workbook should never depend on someone editing a raw file by hand.

In Excel, use Data, Get Data, From File, From Folder and choose Combine and Transform. Power Query appends every file in the folder into one table and adds the file name as a column, which tells you which period each row came from.

Step 2: Write the cleanup once

Every cleanup step you take in the Power Query editor is recorded in the Applied Steps pane and replayed on every refresh. Typical steps for a finance export:

• Remove header and footer rows the system adds to the report.
• Set data types explicitly, especially dates and amounts, so a text date never slips through.
• Trim and clean reference fields, such as invoice numbers, so INV-1042 and INV-1042 match.
• Standardise signs, so receipts and credits follow one convention.
• Keep the source file name and row number, so every result can be traced back.

Name each step in plain language. "Removed report footer" is far easier to review than "Removed Bottom Rows1".

Step 3: Match with merges, strictest rule first

A merge in Power Query is a join between two tables. For a reconciliation, run the strictest match first: same reference and same amount. Then take only what is still unmatched into a second, looser pass, for example same customer and same amount within a few days.

Two join types do most of the work:

• Inner join returns the pairs that match on the rule.
• Left anti join returns the rows on the left that found no partner. That is your unmatched list for the next pass.

Running passes in order matters. A broad rule run first produces matches that look right and are not, and nobody goes looking for them. Running the strict rule first means each later pass only sees what genuinely needs a looser rule.

Step 4: Send exceptions to their own table

Anything that survives every pass goes to an exceptions table with the rule it failed and the source rows. Do not force it into a match. A short list of real exceptions with reasons is the point of the whole exercise.

Step 5: Add control totals before anyone trusts it

Before the output is used, the workbook should prove it read everything. At minimum, compare the row count and total of each source with the original export and with the ledger balance. If they do not agree, the run should be marked as needing review rather than presented as clean. The seven checks guide covers this in detail.

Step 6: Next month, press Refresh All

Save the new exports into the folders and use Data, Refresh All. The same steps run in the same order. The time saved is real, but the bigger gain is that the result is the same no matter who runs it.

When Power Query is not enough

Power Query is the right default for combining, cleaning and matching. It is weaker at row by row decisions with complex conditions, at writing results back into a formatted template, and at very large volumes. Those cases may need a script. The tool comparison explains how to choose.

To see the full pattern working on synthetic data, try the Excel workflow demo.