Power Query merge versus append with reconciled totals

Append stacks compatible records into one table. Merge relates records through matching keys and adds associated information. Choose based on the intended row meaning, then reconcile the result; a successful operation can still duplicate value or drop records.

Monthly sales files often need append because they contain the same kind of record for different periods. Sales lines and customer attributes usually need a key-based relationship or merge because they describe different things.

Append compatible monthly records

The spreadsheet quality lab's monthly files contain five January lines worth 55,500 paise and two February lines worth 17,000. Their combined expected output is seven lines and 72,500 paise.

Validate column meanings and units before append. A NetRupees column is not interchangeable with NetPaise, and a missing column should not become an unnoticed population of null values.

Retain source filenames and verify expected periods. Appending a second copy of January would create duplicate business records even though the file schemas agree. Use the source's replacement or incremental-delivery contract to decide how overlapping files are handled.

Microsoft's combine-files overview describes the repeatable file transformation workflow. The schema and completeness controls determine whether the resulting append is acceptable.

Merge attributes onto a fact table

Use the separate Power BI retail fixture for this example. FactSales has eight lines and 77,500 paise across all statuses. DimCustomer contains a unique row for every CustomerKey used by the fact, including explicit Unknown key 0.

Merge FactSales as the left table with DimCustomer on CustomerKey using a left outer join. Expand Region. Because every fact key matches exactly one dimension row, the expected result still has eight rows and 77,500 paise.

This fixture is intentionally separate from the spreadsheet monthly files; its totals differ. Do not reconcile one dataset against the other's controls.

Inspect matches before expanding

The merged table column represents matching rows from the right side. Before accepting the expansion, inspect match counts. Zero matches indicate an unmatched key; more than one indicates a relationship that can multiply the left row when expanded.

If customer 2 appears twice in the reference table, its two sales lines can each expand twice. A later sum of line value will then be overstated unless the modelling intent explicitly accounts for that many-match relationship.

Microsoft documents merge join kinds, column matching and expansion in its merge-query overview. Physical expansion during data preparation should not be confused with leaving separate dimension and fact tables related in a semantic model.

Choose the unmatched-record policy

A left outer merge preserves sales lines even when their customer mapping is absent. An inner merge removes unmatched lines. Neither is inherently correct for every task; the policy must match the report's population contract.

For a complete sales total, preserving an unresolved customer in an explicit exception or Unknown bucket is often more informative than silently deleting its value. The retail fixture uses Unknown key 0 and retains its 5,000 paid paise.

Use anti-join outputs to inspect unmatched records rather than relying only on a match percentage shown during setup. A small percentage of unmatched records can still contain a material share of value.

Keep reusable dimensions separate when appropriate

Merging every descriptive field into every fact can make a wide table easy to inspect, but a star schema can provide a clearer shared filtering model across several facts. Decide whether the enrichment belongs in source preparation or a reusable dimension relationship.

The choice should preserve grain and support the intended measures. Do not merge a monthly budget onto every daily sales line and then sum it as if it were a line amount.

Verify both operations with different controls

For append, reconcile each source's rows and amount plus the combined total, expected file list and duplicate-key checks. For merge, reconcile left-side rows and amount, match cardinality and unmatched populations before and after expansion.

The supplied scripts verify source arithmetic and keys. Power Query execution and refresh are application-review steps, so record the actual row counts in your environment rather than assuming the example was run there.

Exercise: add a duplicate customer row to a test merge and a repeated January file to a test append. Explain why both can inflate totals even though their causes and appropriate fixes differ.

NeuraPath's Data Analytics with Generative AI course connects data preparation with model design. A reliable workflow chooses the operation from the business grain and proves that the resulting population remains correct.

Continue learning

This article is part of the Power BI data models and reporting sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Build a proper date table for business reporting.
• Continue with Power BI refresh failures: separate credentials from schema changes.