How to Match Purchases to Bank Transactions in WPS Office Power Query
Question details
The user needs a reliable Power Query workflow to match a purchases table to Catholic Syrian Bank transactions by month, allow a small amount variance, and eliminate duplicate fuzzy-match results.

- Product
- WPS Office Power Query
- Device & OS
- not provided
- Scenario
- Bank reconciliation: the user has purchase records and bank statement rows for Catholic Syrian Bank and wants to pair them without manually checking every transaction.
- Observed behavior
- Fuzzy matching currently creates duplicate or inaccurate matches when amounts vary slightly.
Before you start, load both the Purchases and Bank Transactions tables into WPS Office Power Query and verify that the Date and Amount columns are formatted as real dates and numbers. If the bank file contains multiple accounts, filter the bank query to the Catholic Syrian Bank account before creating the matching query.
Create a Controlled Power Query Match Using Month Key, Date Order, and Amount Tolerance
Use this method when fuzzy matching creates duplicate results and you need one best purchase match per bank transaction.
Fuzzy matching alone often returns multiple candidate pairs because it scores text and numeric similarity across the whole table. A controlled Power Query workflow instead creates explicit join keys and filters, so each bank row can keep only the closest purchase.
This process assumes the bank table has already been filtered to the Catholic Syrian Bank account and that the purchase amount is usually within 2 percent of the bank amount.
In WPS Office Spreadsheets, select a cell in the Purchases table and click Data > From Sheet/Range. Repeat for the Bank Transactions table, then use Close & Load To > Only Create Connection so both are available as queries.
In the Power Query Editor, open the Bank Transactions query, select the account column, click the filter dropdown, uncheck Select All, check Catholic Syrian Bank, and select OK.
In both the Purchases and Bank Transactions queries, select the date column, click Add Column > Custom Column, name it MonthKey, and enter a formula such as Date.Year([Purchase Date])*100+Date.Month([Purchase Date]) or the equivalent [Bank Date] formula.
In the Bank Transactions query, click Home > Merge Queries. Select the Purchases query, highlight MonthKey in both tables, choose Inner join, and expand only the needed purchase columns.
Click Add Column > Custom Column, name it AmountDiffPct, and use Number.Abs(([Purchase Amount]-[Bank Amount])/[Bank Amount]). Add a second custom column named DateOrderOK with [Purchase Date] <= [Bank Date]. Filter AmountDiffPct to less than or equal to 0.02 and filter DateOrderOK to TRUE.
Select the bank transaction identifier and date columns, click Home > Group By, add an aggregation for the minimum AmountDiffPct, and then merge the result back to keep the purchase record with that smallest difference. Review any rows that still tie.

Tune the 2% Tolerance and Review Remaining Ties
Use this after the controlled match when the statement contains bank charges, rounded amounts, or multiple equal-value purchases.
Match Bank Transactions in WPS Office Power Query Without Duplicate Fuzzy Results
Use WPS Office Power Query to combine purchase and bank tables with controlled date, account, and tolerance rules, then keep the smallest amount difference for each bank transaction. WPS Spreadsheets gives you the same Power Query workflow in a free, lightweight Office package with Excel-compatible files.
- 1. Load the Purchases table: Open WPS Spreadsheets and click Data > From Sheet/Range to load the Purchases table.
- 2. Load and filter the Bank Transactions table: Load Bank Transactions, open the Power Query Editor, and filter the account column to Catholic Syrian Bank.
- 3. Merge with controlled keys: Add MonthKey and AmountDiffPct custom columns to both queries, then merge on MonthKey.
- 4. Apply date and amount rules: Filter the merged table to Purchase Date ≤ Bank Date and AmountDiffPct ≤ 2%.
- 5. Keep the closest match: Group by bank transaction, keep the smallest difference, and manually review any remaining ties.

Frequently Asked Questions
Why does Power Query fuzzy matching create duplicate matches for bank reconciliation?
Because fuzzy matching scores many similar rows and can return more than one candidate for the same bank transaction. Instead, create an explicit MonthKey merge and filter by date order and amount tolerance, then keep the smallest difference per bank row.
How can I match purchases and bank transactions when amounts are not exactly the same?
Add a custom AmountDiffPct column using Number.Abs(([Purchase Amount]-[Bank Amount])/[Bank Amount]) and filter it to about 0.02. Adjust the threshold if the bank data has more or less variance.
What date rules should I use for matching purchases to bank statement rows?
Use these two rules: the purchase and bank dates must have the same year and month, and the Purchase Date must be on or before the Bank Date. You can enforce both with a MonthKey column and a date-order filter.
How do I filter Power Query to only one bank account before matching?
Open the Bank Transactions query in the Power Query Editor, click the dropdown arrow in the account column, uncheck Select All, check Catholic Syrian Bank, and select OK.
What should I do if multiple purchases still match one bank transaction after filtering?
Group the rows by bank transaction and keep the smallest absolute amount difference. If exact ties remain, add a tie-breaker such as the earlier Purchase Date, or review those rows manually and adjust the tolerance.




