Excel Power Query for Accountants: Reconcile Two Registers without Repeated Copy-paste

Scope: Practical Excel Power Query guide; menu availability and platform support depend on the installed Excel version. Verification date: 11 October 2026.

Start with the question the reconciliation must answer

Two registers can contain the same total while holding different invoices. A reliable reconciliation checks which records match, which are missing and which have amount differences. Power Query helps repeat the same data-cleaning and matching process without recreating it every month.

Microsoft documents merge operations using common columns and selectable join types. The merge does not decide whether a match is commercially correct: the accountant must design the key and review the output.

Prepare two clean source tables

For a fictional supplier reconciliation, use a books table and a supplier table. Include supplier code, invoice number, invoice date, taxable amount and tax amount as relevant. Preserve the raw files before cleaning. Remove report headings and total rows from the transaction population.

Keep identifiers as text where appropriate, especially if leading zeros matter. Trim unwanted spaces and apply a documented case convention. Do not remove meaningful punctuation from invoice numbers without checking whether it distinguishes actual documents.

Define the matching key

Invoice number alone may repeat across suppliers. Use supplier identity plus invoice number, and include other fields when the dataset requires them. Matching amount should often be a comparison field rather than part of the key, so an amount difference remains visible.

Test duplicates on both sides before merging. If two rows in each table share the same key, expanding their matches can produce four rows. Decide whether those are legitimate line items requiring aggregation or duplicate records requiring investigation.

Run the reconciliation

1. Load each source table into Power Query.
2. Set consistent data types and clean the chosen key fields.
3. Check duplicate keys and record how they are handled.
4. Use a left outer merge to keep every books row and bring in matching supplier data.
5. Expand the required comparison columns.
6. Calculate amount differences and identify missing matches.
7. Create an anti-join or equivalent report for supplier rows absent from books.

Keep the two unmatched populations separate. An inner join alone can hide exactly the missing records the accountant needs to investigate.

A worked matching example

The ₹500 difference may reflect an entry error, credit note or different comparison basis. Investigate the invoice instead of automatically changing either register. Missing records need the same evidence-led approach.

Make the process repeatable

Save the query and source-location conventions. For the next period, supply the expected files and refresh. Review schema changes, row counts and totals after refresh; a repeatable process can repeatedly produce a wrong answer if its inputs change unnoticed.

Retain the raw registers, refresh date and resolved exception report. Follow appropriate data-access and privacy settings when combining sources. Avoid fuzzy matching as an automatic approval mechanism for legal invoice records.

Use the result in financials preparation

The approved reconciliation can support receivable, payable and tax workings used alongside assureOffice. It does not itself post accounting entries or prove a tax conclusion. Clear keys and visible exceptions make Excel a useful supporting tool within a structured financial-statement workflow.

Design an exception report people can use

Add an issue category, responsible person, evidence reference and resolution status to the reviewed exception output. Keep the refreshed query result separate from manually entered review comments so a refresh does not erase the investigation record. Use stable keys to connect comments back to the source rows.

For a recurring monthly reconciliation, compare this month's unresolved items with the previous period. An old unmatched invoice may have been resolved through a later entry, while a new duplicate may need fresh investigation. The query should support that review cycle rather than simply producing the same unexplained differences each month.

Related articles

• Trade Receivables Ageing under Schedule III: A Worked Excel Example
• Trade Payables Ageing: Building the Working and Checking MSME Classification
• AIS, TIS and Form 26AS Do Not Match the Books: How to Reconcile Them

Sources and references

• Microsoft Support — merging queries in Excel Power Query
• Microsoft Learn — Power Query merge operations