Power Query Merge repeats bank amounts: check invoice matches

If merging bank rows with invoices in Power Query increases the total, inspect the number of matches per bank row before expanding the joined table. One bank payment can legitimately relate to several invoices; expansion repeats the bank amount beside each invoice. Deleting repeated-looking rows can then discard valid invoice allocations without fixing the relationship.

This guide concerns Merge, a join between related tables, rather than Append, which stacks files. If the bank source is PDF-only, LocalExtract can prepare a local CSV. It does not create invoice references or allocate payments for you.

Keep bank-event totals separate from allocation totals

Microsoft’s merge overview explains matching columns and join types. Table.NestedJoin stores matched rows in a nested table; expanding that table can create multiple output rows for one outer row.

Give each source bank event a stable working ID. Choose the join key only after verifying the relationship between the bank evidence and invoice allocation records. A date or amount is not automatically a unique payment reference. Confirm account, currency and the meaning of the reference as well as its data type.

Reproduce a 150.00 USD total becoming 250.00 USD

These invented records explicitly establish the reference relationships. B1/B2 are bank-event aliases; R-A/R-B are teaching join keys, not universal banking fields.

The invoice allocation table assigns 60.00 USD to I1 and 40.00 USD to I2 under R-A, and 50.00 USD to I3 under R-B. Expanding those matches produces:

Summing the repeated bank column gives 250.00 USD, although only 150.00 USD arrived in the two bank events. The allocation column still totals 150.00 USD. If you remove the second R-A row as a “duplicate,” you lose I2 and retain only 110.00 USD of allocations. Neither a repeated bank ID nor a repeated amount makes an invoice row disposable.

Count nested matches before expansion

In Power Query Editor, select the bank query and use Home → Merge Queries. Choose the allocation query and the verified matching columns in the same order. For the investigation, Left outer retains the bank population; an inner join can hide unmatched bank rows.

Rename the resulting nested-table column to Allocations for this illustration. Add a custom column using Table.RowCount([Allocations]), based on Microsoft’s Table.RowCount reference. The name is your chosen query column, not a bank-export requirement. Check the result before using the expansion icon.

If the same reference occurs on multiple bank payments as well as multiple invoices, a many-to-many join may create additional combinations. Stop and establish the actual allocations instead of accepting every combination as a payment-to-invoice link.

Choose the output grain before calculating

For a bank-event report, retain one bank amount per established event and keep an allocation summary linked to it. For an invoice-allocation report, retain the invoice rows and sum allocated amounts; mark the copied bank amount as contextual, not an additive measure. Retain the detailed source relationships even if a summary is the final deliverable.

Compare allocated and bank amounts per event, not just across the whole file. A short allocation on B1 and an over-allocation on B2 can cancel in the grand total. Unexplained fees, partial payments or currency differences remain explicit unresolved items, not amounts invented to make the groups agree.

The synthetic join-cardinality exercise contains both source tables, expanded rows and an unmatched-row control. Python reproduces the relationships and totals; it does not execute an M query or validate actual invoice matching.

Use the file-combination guide for Append and the overlapping-export guide for repeated source observations. Neither operation substitutes for checking the cardinality of this Merge.

LocalExtract handles one PDF at a time and exports date,description,amount. It does not join invoices, assign payment references or synchronize accounting records. Download LocalExtract for a PDF source.

Microsoft documentation checked on October 15, 2026.