How to Reconcile Money in Excel Transactions with Power Query
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.

- 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 begin, ensure you have created a secondary 'Reconciled' table in your workbook to safely store transactions that have already been cleared.
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.
Set up a separate Excel table named 'Reconciled' and manually copy any fully reconciled transactions from your dynamic feed into this table.
Select your dynamic transaction table, go to the 'Data' tab, and click 'From Table/Range'. Repeat this step for your new 'Reconciled' table.
In the Power Query Editor, open the dynamic transaction query. Navigate to the Home tab and click 'Merge Queries'.
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.
Click 'Close & Load' to generate a new worksheet containing only your outstanding, unreconciled transactions.

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. Download WPS Office: Install the free WPS Office suite on your preferred device.
- 2. Open your financial workbook: Launch WPS Spreadsheet and seamlessly open your existing .xlsx financial tracking files.
- 3. Utilize data tools: Use built-in data filtering, VLOOKUP, or conditional formatting to effortlessly manage your outstanding transactions.

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.




