Missing data after a merge almost always comes down to join types and mismatched keys, but chasing a 7,000-value gap in Power Query doesn't require guesswork. The frustration here is familiar: you're working with large datasets, the merge runs without errors, yet the numbers don't line up. That 7,000-unit discrepancy isn't a mystery, it's a debugging path with clear signposts, provided you step back from panic and examine the joins systematically.
The most common culprit is using an inner join when you need a left outer join. Power Query defaults to inner joins for many merges, which silently discards rows from the first table that don't have matching keys in the second. If your original table sums to 100,000 and your merged result sums to 93,000, those 7,000 lost values are likely rows in your primary table that found no partner. The fix isn't complicated: change the join kind to "Left Outer" in the Merge dialog. But identifying which rows were dropped requires Post-merge diagnostics. Use the "Table.Profile" function or add a custom column counting nulls in key fields from the second table, rows with nulls in that column are the orphans.
A second reason is key mismatch beneath the surface. Spaces, inconsistent formatting, or data type differences, number stored as text, trailing spaces from imports, cause "matching" keys to fail silently. You can expose these by duplicating the original table before the merge, then applying a "Remove Duplicates" on the key column. Compare the result with the merged table's unique keys. The difference reveals rows that had no match, even if they looked identical. For large datasets, writing a simple query that uses "Table.SelectRows" to filter for non-matching keys is faster than manual scanning.
Finally, check whether your aggregation introduces the error. Summing a column after a merge may include duplicate rows if the second table had multiple matches for a single key. Power Query expands those rows, inflating the count in ways that mask the missing 7,000. Validate by running a count of distinct keys in both tables before merging. If the counts differ, correct the join logic first. Debugging merges is mechanical: walk from join type to key integrity to expansion behavior. You will find those 7,000 values in the gap between what you assumed matched and what actually did.