When a merge produces numbers that look too good

You combine a sales export with a customer file. The output looks tidy, but revenue has increased even though no sale was added. Stop and investigate: multiple matches may have repeated invoices. An analysis is only reliable when you can explain the path from the source files to the final table.

Before manipulating anything, define the expected result: one row per invoice, customer or order line? These are different levels of detail. Write the chosen unit of observation at the top of your working document.

Set the rules before removing anything

Keep original exports in a dated folder and work on copies. Microsoft explains that removing duplicates permanently deletes the selected rows, so flagging repetitions is preferable to removing them automatically. Two rows with the same name may describe different customers; two invoice lines with identical amounts may both be valid.

Choose a stable identifier. Invoice numbers may need to be combined with the issuing business or financial year. Order lines also need their line number. Document missing identifiers instead of silently replacing them with customer names.

Standardise, match, then reconcile

Prepare copies of the matching columns: unwanted spaces, date formats, decimal separators and leading zeros. An identifier such as 00127 must remain text when those zeros are meaningful. Retain the original values so that each correction can be explained.

Microsoft describes several Power Query joins: a left join keeps the main table’s rows, while an inner join retains only matches. Choose according to the question. To check every invoice, preserve them and isolate those whose customer cannot be found. Then compare row counts and monetary totals before and after the merge.

Fictional example: one duplicate adds €10,000

A fictional export contains 100 invoices totalling €120,000. Customer C014 appears twice in the reference file, and its eight invoices total €10,000. A join followed by expansion repeats those eight rows: the output contains 108 rows and €130,000. The issue is in the reference data, not commercial growth.

The team checks both customer records, selects the authoritative one and preserves a mapping between old and new identifiers. Repeating the merge restores the original 100 invoices and €120,000 total. Invoices without a matching customer stay on an exception list for investigation.

A practical protocol for next week

Start with two files, one period and one question. Success means a repeatable merge, not the largest possible number of combined columns.

  • Record each export’s date, scope, row count and control total.
  • Define the identifier and verify uniqueness in the reference table expected to contain one record per entity.
  • Manually inspect ten matches, including similar names and missing values.
  • Keep a transformation log and an exception list with an assigned owner.
  • Run the process against a second export to check that it works without improvised corrections.

Where human judgement remains necessary

Similar names do not prove identity. Approximate matches should remain proposals for review, especially when money or people are involved. Never turn missing values into zeros without an explicit business rule. If files contain personal information unrelated to the question, remove those columns from the analytical copy and restrict who can access it.