Power Query Problems

How to Reconcile Money in Excel Transactions with Power Query

Amos GikundaAmos Gikunda Nov 3, 2026 869 views

Question details

The user needs to reconcile dynamic bank-feed transactions in Money in Excel without having their custom reconciliation marks overwritten or invalidated when new data syncs.

How to Reconcile Money in Excel Transactions Using Power Query
Product
Money in Excel
Device & OS
not provided
Scenario
Managing and reconciling dynamic bank-feed transactions.
Observed behavior
Adding reconciliation marks directly to the dynamic transaction table results in data loss, as the marks are overwritten or invalidated when the bank-feed updates.
Before you start

Before you begin, ensure you have created a secondary 'Reconciled' table in your workbook to safely store transactions that have already been cleared.

Solution 1Recommended

Isolate Outstanding Transactions Using a Power Query Anti-Join

Use a Power Query Left Anti-Join to compare your dynamic bank feed against a static reconciliation table, effectively filtering out cleared items.

Money in Excel uses a dynamic table for bank feeds. Placing your marks directly in this table causes them to be overwritten upon refreshing.

By utilizing an anti-join, you can instruct Excel to only show transactions that are present in your dynamic feed but absent from your completed reconciliation table.

1
Create a reconciliation table

Set up a separate Excel table named 'Reconciled' and manually copy any fully reconciled transactions from your dynamic feed into this table.

2
Load data into Power Query

Select your dynamic transaction table, go to the 'Data' tab, and click 'From Table/Range'. Repeat this step for your new 'Reconciled' table.

3
Initiate a Merge Query

In the Power Query Editor, open the dynamic transaction query. Navigate to the Home tab and click 'Merge Queries'.

4
Configure the Left Anti-Join

Select the 'Reconciled' table from the second dropdown. Click the matching transaction ID columns in both previews. Under 'Join Kind', select 'Left Anti (rows only in first)' and click OK.

5
Load the outstanding items

Click 'Close & Load' to generate a new worksheet containing only your outstanding, unreconciled transactions.

Isolate Outstanding Transactions Using a Power Query Anti-Join
Data Preserved: Since your reconciliation table is separated from the dynamic feed, future syncs will no longer erase your progress.
Free Microsoft Office alternative

Reconcile Financial Data Easily with WPS Office

While Money in Excel and Power Query are specific Microsoft tools, WPS Office provides a lightweight, highly compatible alternative. Enjoy advanced spreadsheet capabilities to track, filter, and reconcile your financial data without expensive subscriptions.

  1. 1. Download WPS Office: Install the free WPS Office suite on your preferred device.
  2. 2. Open your financial workbook: Launch WPS Spreadsheet and seamlessly open your existing .xlsx financial tracking files.
  3. 3. Utilize data tools: Use built-in data filtering, VLOOKUP, or conditional formatting to effortlessly manage your outstanding transactions.
Free, lightweight, and incredibly fast to installFully compatible with Microsoft Excel file formats (.xlsx)Advanced VLOOKUP and data filtering for easy financial reconciliationFamiliar user interface with zero learning curve
QA img-9

Frequently Asked Questions

Why do my reconciliation marks disappear in Money in Excel?

Money in Excel uses dynamic bank-feeds that routinely refresh. Any custom marks added directly to the dynamic table can be overwritten or deleted during the sync process.

What is a Power Query Left Anti-Join?

A Left Anti-Join is a Power Query merge operation that returns only the rows from the first (left) table that do not have matching rows in the second (right) table, making it perfect for finding unreconciled items.

Can I manually reconcile without Power Query?

Yes, you can manually copy transactions to a static sheet and use standard VLOOKUP or conditional formatting to highlight duplicates, though it requires more manual effort than Power Query.

Does copying data to a separate reconciliation table increase file size?

It slightly increases the file size, but it is a necessary structural step to protect your historical reconciliation data from being wiped out by dynamic bank feed updates.