The dashboard shows 1,248 orders. The operations export shows 1,231. The meeting starts in twenty minutes, and someone asks which number to put on the slide. Before choosing, make sure the two reports are answering the same question. Then find the records that explain the gap.
This guide walks through a fictional order-count discrepancy. You can use the same method when two reports disagree on applications, customers or other records that have a reliable identifier.
1. Agree on what counts
Write one sentence that a colleague could use to reproduce the number. For example, “Count each distinct order placed in October, using South African local time, and exclude test and cancelled orders as of the reporting cut-off.” Record the source extracts, their refresh times and the exact cut-off.
“Placed in October” and “shipped in October” answer different questions. The same is true of orders recorded before month-end and an export that stopped refreshing at noon. If the measures are intentionally different, label them clearly. If they should agree, apply the same rules before comparing records.
2. Match IDs and look in both directions
Work from copies of the original extracts. Decide what one row should represent in the comparison. An order can legitimately have several product lines. If you are comparing order counts, check for blank IDs and repeated IDs, then build a distinct list of valid order IDs on each side. Keep the original rows so you can investigate any exceptions.
Look for IDs present only in the dashboard extract and then for IDs present only in the operations export. A net difference of 17 could hide 23 IDs found only on one side and six found only on the other. A zero gap could still hide missing records in both directions.
For a value comparison, such as total order value, compare amounts for matched IDs as well. First agree on currency and whether the amounts include discounts or taxes. Keep any records with missing or ambiguous IDs in a separate review list until you can resolve them.
3. Explain the difference line by line
In this fictional example, 1,225 order IDs appear in both reports. The dashboard also has 23 IDs that the export lacks, while the export has six IDs that the dashboard lacks.
The source records show that 12 of the dashboard-only orders were entered after the export’s early refresh. Nine were cancelled by the agreed cut-off. The other two went through a sales channel the export missed. The six export-only IDs were test orders that should have been excluded.
Start with the export’s 1,231 orders. Add the 12 late entries and the two orders from the omitted channel. Subtract the six test orders. The agreed count is 1,239. The dashboard arrives at the same result when you remove the nine cancelled orders from 1,248.
Record the adjustment, the IDs behind it and the evidence that supports it. If something is still unexplained, give it a named owner and a review date. Keep it visible on the reconciliation instead of burying it in an adjustment labelled “other”.
4. Repeat the check in Power Query
Load the two extracts as separate queries. Use compatible data types for the matching columns and preserve leading zeroes when they are part of an ID. To find dashboard-only IDs, merge the dashboard query on the left and choose a Left anti join. Reverse the query order and run the same join to find export-only IDs. Microsoft’s Power Query merge guide documents the join types and how to match columns.
Once the first reconciliation is complete, keep a short summary alongside each refresh. Include both source totals, the two exception counts, the agreed total and anything still open. A recurring exception often tells you exactly which filter, feed or refresh schedule needs attention.
Bring us one number you keep debating
If your team spends every month comparing spreadsheets before it can make a decision, bring The Spokesdude Network the two reports, the KPI that keeps changing and the decision it supports. We can help you trace the difference and build a reporting process your team can repeat.
