Power Query Problems

How to Match Purchases to Bank Transactions in WPS Office Power Query

Phi Hung VoPhi Hung Vo Nov 13, 2026 869 views

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.

How to Match Purchases to Bank Transactions in WPS Office Power Query
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

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.

Solution 1Recommended

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.

1
Load both tables into Power Query

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.

2
Filter the bank query to Catholic Syrian Bank

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.

3
Add month-year keys to both queries

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.

4
Merge the queries on MonthKey

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.

5
Add and filter the amount-difference and date-order rules

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.

6
Keep the smallest difference for each bank row

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.

Create a Controlled Power Query Match Using Month Key, Date Order, and Amount Tolerance
Duplicate matches are controlled: This workflow replaces loose fuzzy matching with explicit filters, so only the closest qualifying purchase remains for each bank transaction.

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. 1. Load the Purchases table: Open WPS Spreadsheets and click Data > From Sheet/Range to load the Purchases table.
  2. 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. 3. Merge with controlled keys: Add MonthKey and AmountDiffPct custom columns to both queries, then merge on MonthKey.
  4. 4. Apply date and amount rules: Filter the merged table to Purchase Date ≤ Bank Date and AmountDiffPct ≤ 2%.
  5. 5. Keep the closest match: Group by bank transaction, keep the smallest difference, and manually review any remaining ties.
Controlled Power Query matching reduces duplicate fuzzy results for bank reconciliations.Opens and saves .xlsx, .xls, and .csv bank and purchase files with Microsoft Office format compatibility.Familiar ribbon and Power Query editor make Excel workflows easy to migrate.Free, lightweight WPS Office install runs on Windows, macOS, Linux, Android, and iOS.
microsoft office alternative - wps office

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.