Combining Data with Merges and Appends

Introduction

Business intelligence rarely depends on a single, perfectly structured data source. A sales dashboard may require transaction records from several branches, customer attributes from a CRM system, product information from an ERP platform, and targets maintained in spreadsheets. Power Query provides two fundamental operations for combining such data: append, which adds rows, and merge, which adds columns by matching records.

These operations are conceptually simple but require careful data modelling. Incorrect keys, inconsistent data types, duplicate records, and incompatible schemas can produce incomplete or misleading reports without generating an obvious technical error. Effective integration therefore involves both technical implementation and business interpretation.

This chapter explains how to append and merge data in Power Query, how to select appropriate matching keys, how to manage relationship cardinality, and how to reconcile information from multiple business systems.

Learning Objectives

By the end of this chapter, you should be able to:

• distinguish append operations from merge operations;
• append tables with compatible and incompatible schemas;
• perform inner, left outer, right outer, full outer, left anti, and right anti joins;
• select stable and meaningful matching keys;
• diagnose duplicate matches and unexpected row multiplication;
• reconcile records from multiple business systems;
• validate integration results using counts, exceptions, and business rules;
• apply professional governance and documentation practices.

1. The Two Fundamental Combination Operations

1.1 Appending: combining rows

An append operation places the rows of one table below the rows of another table. It is appropriate when tables represent the same type of entity or event and have a broadly similar structure.

For example, monthly sales files may contain the same columns:

Appending January, February, and March tables creates one longer sales table. The operation increases the number of rows; it does not match records based on a key.

Power Query aligns columns by column name rather than by physical position. If one source contains a column absent from another, Power Query creates the column and places null values in the missing cells. This is useful for evolving sources but requires review because nulls may represent either “not applicable” or missing data.

1.2 Merging: combining columns

A merge operation compares two tables using one or more matching columns and returns columns from the related table. It is conceptually similar to a relational database join.

For example, a Sales table may contain ProductCode, while a Products table contains ProductCode, ProductName, Category, and StandardCost. Merging Sales with Products adds descriptive product fields to each sale.

A merge can change the number of rows if the matching column is not unique in the related table. This is a central difference from a simple lookup assumption: a key that matches multiple rows can produce multiple output rows.

1.3 A practical decision rule

Use append when the question is: “How can I place records of the same kind into one table?”

Use merge when the question is: “How can I bring attributes or related records from another table into this table?”

A typical model uses both operations. Regional sales files may first be appended into one fact table; that table may then be merged with product, customer, or exchange-rate reference tables.

2. Appending Tables in Power Query

2.1 Append as new query and append queries

Power Query offers two common choices:

• Append Queries modifies or uses an existing query as the basis of the combined result.
• Append Queries as New creates a separate query and preserves the original queries unchanged.

The second option is generally safer during development because source queries remain available for testing and auditing.

The append dialog supports two-table and three-or-more-table append operations. For maintainability, a folder-based ingestion pattern is often preferable when files share a consistent structure. A single query can retrieve all files, apply a transformation to each file, and append the transformed results.

2.2 Schema alignment

Suppose the January table contains Revenue and the February table contains SalesAmount. Power Query treats these as different columns even if they mean the same thing. The combined table will contain both fields, with nulls where each source lacks the other field. This is a semantic problem, not merely a formatting issue.

Before appending, standardise:

• column names;
• data types;
• date and time conventions;
• currency and unit definitions;
• treatment of blank and null values;
• categorical labels such as “UK”, “United Kingdom”, and “GB”.

Column order is not normally important, but column meaning is essential.

2.3 A simple M example

The following M expression appends three existing queries:

Table.Combine({JanuarySales, FebruarySales, MarchSales})

When schemas differ, an explicit transformation may be required before the append:

Table.RenameColumns(FebruarySales, {{"SalesAmount", "Revenue"}})

A robust design usually applies this standardisation within each source query, rather than attempting to repair all inconsistencies after the append.

2.4 Append validation

After an append, check:

1. the expected row count;
2. the number of source files or periods represented;
3. duplicate transaction identifiers;
4. null rates in important fields;
5. totals by source and period;
6. whether the same transaction was loaded twice.

If source tables are mutually exclusive, the combined row count should equal the sum of their row counts. If records overlap, this expectation does not apply and duplication must be assessed using business identifiers.

3. Merging Tables and Join Types

A merge requires a primary table, a related table, and one or more matching columns. The selected columns must have compatible data types. If a composite key is used, the columns must be selected in corresponding order in both tables.

3.1 Inner join

An inner join returns only rows that have a match in both tables. It is useful when unmatched records should be excluded, such as analysing sales only for products that exist in an approved product master.

Its risk is silent data loss. Orders with an invalid ProductCode disappear from the result, so an inner join should not be used merely to hide exceptions.

3.2 Left outer join

A left outer join retains every row from the first, or left, table and adds matching information from the second table. Unmatched rows receive null values for the added columns.

This is the most common join for enriching a fact table. For example, all sales should normally remain visible even when a product master record is missing.

3.3 Right outer join

A right outer join retains every row from the second table. It is logically equivalent to reversing the tables and using a left outer join. It can be useful when the right-hand table is the primary focus, although many developers prefer reordering the tables for clarity.

3.4 Full outer join

A full outer join retains all rows from both tables. It is especially useful for reconciliation, because it exposes records found only in the first source, only in the second source, and in both.

3.5 Left anti join

A left anti join returns rows from the first table that have no match in the second. It answers questions such as:

• Which customers in the CRM are absent from the finance system?
• Which product codes in sales are not in the product master?

3.6 Right anti join

A right anti join returns rows from the second table that have no match in the first. It is useful for identifying records present in a reference or comparison source but absent from the primary source.

3.7 Join selection table

After selecting a join, Power Query creates a nested table column. The expand button allows selected fields to be brought into the main table. Avoid expanding unnecessary fields because wider tables increase complexity and can introduce duplicate names.

4. Matching Keys and Data Quality

4.1 What makes a good key?

A matching key should be:

• unique where uniqueness is expected;
• stable over time;
• complete, with few or no null values;
• consistent across systems;
• business-relevant or supported by a documented technical mapping;
• appropriately granular for the intended relationship.

A customer name is generally a weak key because spelling, punctuation, legal changes, and shared names create ambiguity. A system-generated CustomerID is preferable, provided both systems use the same identifier or a reliable cross-reference exists.

4.2 Natural and surrogate keys

A natural key has meaning in the business, such as an invoice number or national product code. A surrogate key is an artificial identifier generated for modelling, such as an integer CustomerKey.

Natural keys can be useful for source reconciliation but may change or be reused. Surrogate keys improve model control but require a mapping process. A professional integration solution often retains both: the source identifier for traceability and the model key for relationships.

4.3 Composite keys

Sometimes no single column identifies a record. A schedule may be uniquely identified by BranchCode, ProductCode, and EffectiveDate. These fields form a composite key.

A composite key must be constructed consistently. For example, concatenation can be performed only after trimming and standardising each component. Separators should be used to reduce ambiguity. The following values should not accidentally become indistinguishable:

• Branch = AB, Number = 12;
• Branch = A, Number = B12.

A safer design is often to retain separate key columns and select all of them in the merge dialog.

4.4 Data preparation before matching

Common preparation steps include:

• converting both key columns to the same data type;
• trimming leading and trailing spaces;
• cleaning non-printing characters;
• standardising case where the business key is case-insensitive;
• preserving leading zeroes in codes;
• normalising date and time zones;
• removing accidental decimal formatting from identifiers.

Do not automatically remove punctuation or alter identifiers without understanding their meaning. For example, “AB-12” and “AB12” may be equivalent in one system but different products in another.

5. Cardinality and Row Multiplication

Cardinality describes how many records on one side can relate to records on the other side.

A common Power Query pattern is many-to-one: many sales rows match one product master row. If the product master contains duplicate ProductCode values, a sales row may expand into two or more rows when the merged table is expanded. Revenue and quantity can then be overstated in reports.

5.1 Diagnosing duplicate keys

Before merging, group the lookup table by its intended key and count rows. A key expected to be unique should have a maximum group count of one. In Power Query, this can be done with Group By, using the key column and a row-count aggregation.

For a table with key values k, the frequency of key k can be represented as:

A uniqueness rule requires f(k) ≤ 1 for every key value k. This condition applies only when the table is intended to contain one record per key. A transactional table may correctly contain repeated keys.

5.2 Managing legitimate one-to-many relationships

If multiple matches are legitimate, do not arbitrarily remove duplicates. Instead:

• keep the related records in a nested table;
• aggregate them using a documented rule;
• filter by validity date or status;
• select the current record according to a defined priority;
• redesign the model using a bridge table where appropriate.

For example, a customer may have several addresses. Selecting the “first” address is not a valid business rule unless the source provides a meaningful ordering, such as PrimaryAddress = Yes or the latest effective date.

6. Reconciliation Across Business Systems

Reconciliation is more than joining two tables. It is the controlled comparison of populations, attributes, and measures from different systems.

6.1 Typical reconciliation dimensions

A finance system and a sales system may disagree about:

• transaction existence;
• customer or product identity;
• transaction date;
• quantity;
• tax treatment;
• currency;
• status;
• cancellation or refund treatment.

A full outer merge can create a comparison table with indicators such as:

• “Only in sales”;
• “Only in finance”;
• “Matched”;
• “Matched but amount differs”.

The comparison must define tolerances and business rules. A difference of £0.01 may be rounding, while a difference of £10,000 requires investigation.

6.2 Source precedence

When systems disagree, establish a source-of-truth policy. For example:

• the ERP may govern invoiced revenue;
• the CRM may govern customer contact details;
• the warehouse system may govern shipped quantity;
• the product master may govern category classification.

Source precedence should be documented rather than embedded invisibly in conditional transformations.

6.3 Auditability

Retain source-system identifiers and, where practical, source-file names, extraction timestamps, and transformation status. These fields support investigation and allow a reported value to be traced back to its origin.

7. Applied Case: Regional Sales and a Product Master

Background

A fictional retailer, Northstar Home, receives weekly sales files from four regions. It also maintains a central product master. Management wants a Power BI report showing sales by product category and region.

Problem

The regional files use the same basic structure, but one region calls the revenue field SalesValue. Product codes are stored as text in three files and as numbers in the fourth. The product master contains two records for product P204 because an old category assignment was not closed. Some sales records contain product codes that are absent from the master.

Analysis

The regional sales files represent the same business event type, so they should be appended. The product master is a descriptive table, so it should be merged using ProductCode. However, the duplicate P204 means the master is not unique, and the missing product codes must not be silently discarded.

Approach and solution

1. Standardise each regional query. Rename SalesValue to Revenue, convert ProductCode to text, trim whitespace, and assign a Region column.
2. Append the four regional queries. Use Append Queries as New to create RegionalSales.
3. Profile the product master. Group by ProductCode and identify duplicate keys.
4. Resolve the duplicate P204. The business owner confirms that the current record is the one with the latest EffectiveFrom date. The transformation keeps the current valid record and archives the old record separately.
5. Merge RegionalSales with the cleaned product master. Use a left outer join so every sale remains available.
6. Expand ProductName and Category. Add a MatchStatus column indicating whether Category is null.
7. Create an anti-join exception query. Return sales product codes absent from the product master for data-quality follow-up.
8. Validate totals. Compare total Revenue before and after the merge. Since the product master is now unique by ProductCode, the merge should not multiply sales rows.

Outcome

The report includes all regional sales, categorises valid product codes, and separately displays unresolved codes. Management receives a more reliable category analysis, while data stewards receive an actionable exception list rather than an apparently complete but inaccurate report.

Learning points

The case illustrates that integration is a controlled process, not simply a button-clicking exercise. Schema standardisation supports appending; key governance supports merging; anti-joins expose exceptions; and pre- and post-merge validation protects measures from duplication.

8. Implementation and Validation Best Practices

A professional Power Query solution should:

• keep raw extraction queries separate from transformation queries;
• use descriptive query and step names;
• standardise data types early;
• avoid merging on display labels when stable identifiers exist;
• test lookup uniqueness before expansion;
• preserve source keys for auditability;
• create explicit exception queries;
• document assumptions about nulls, duplicates, dates, and currencies;
• validate row counts and key measures after major operations;
• disable unnecessary loads for staging queries;
• parameterise file paths and environment-specific settings;
• avoid hard-coded filters that silently exclude new data.

A useful validation design includes three levels:

1. Structural validation: Are the expected columns, data types, and files present?
2. Relational validation: Do keys match, and are cardinality assumptions satisfied?
3. Business validation: Do totals, statuses, and operational rules make sense?

9. Limitations, Governance, and Contemporary Practice

Power Query transformations occur during data refresh and may be affected by source-system availability, privacy settings, gateway configuration, and query-folding behaviour. Large merges can also consume significant memory, particularly when keys are poorly selected or lookup tables are unnecessarily wide.

Privacy and governance are important when combining customer or employee data. Apply least-privilege access, protect personal identifiers, and avoid exposing fields that are not required for the analytical purpose. Data retention and regulatory requirements may apply to both source and transformed datasets.

Contemporary data platforms increasingly use dataflows, lakehouses, semantic models, and automated data-quality monitoring. AI-assisted features may suggest transformations or detect anomalies, but suggestions must be reviewed. An AI tool cannot determine, without business context, which system is authoritative or whether two similarly named customers are legally the same entity.

Chapter Summary

Appending combines rows from structurally similar tables, whereas merging combines columns by matching keys. Append operations depend on schema standardisation, while merge operations depend on key quality and relationship cardinality. Power Query supports inner, outer, full, and anti joins, each serving a different analytical or reconciliation purpose.

Reliable integration requires compatible data types, stable identifiers, documented composite keys, and explicit treatment of duplicates. A non-unique lookup key can multiply rows and inflate measures. Full and anti joins are valuable for reconciliation and exception management. Professional solutions preserve source identifiers, validate structural and business outcomes, document source precedence, and protect sensitive information.

Key Terms

Append: An operation that combines rows from two or more tables.

Merge: An operation that combines tables by matching one or more columns.

Join: A relational operation that identifies corresponding records across tables.

Key: A column or set of columns used to identify or match records.

Natural key: An identifier that has business meaning.

Surrogate key: An artificial identifier created for modelling or integration.

Composite key: A key formed from multiple columns.

Cardinality: The number of records that can relate between two tables.

Inner join: A join returning only records with matches in both tables.

Left outer join: A join retaining every record from the left table.

Full outer join: A join retaining records from both tables.

Anti join: A join returning records without a match in the other table.

Lookup table: A table providing descriptive or reference attributes.

Fact table: A table containing measurable events or transactions.

Schema: The structure, columns, and data types of a table.

Reconciliation: A controlled comparison of records or measures from different sources.

Source of truth: The system designated as authoritative for a particular data element.

Data lineage: The traceable path from an output value to its source and transformations.

Query folding: The process by which Power Query pushes transformations back to the source system where supported.

Review Questions

5 Short Answer Questions

1. What is the difference between an append and a merge in Power Query?
2. Why should key columns have compatible data types before a merge?
3. What does a left anti join identify?
4. Define many-to-one cardinality and provide one business example.
5. Why can duplicate lookup keys create overstated measures?

5 Long Answer Questions

1. Explain the main Power Query join types and discuss an appropriate business use for each.
2. Describe a systematic process for preparing regional files before appending them.
3. Discuss the advantages and risks of natural keys, surrogate keys, and composite keys.
4. Explain how a full outer merge can support reconciliation between two business systems.
5. Develop a validation framework for an append-and-merge data integration process.

3 Analytical / Application Questions

1. A product master contains three records for one ProductCode, while sales contains thousands of transactions using that code. Analyse the possible effects of merging and propose two defensible solutions.
2. A CRM and finance system use different customer identifiers and contain slightly different customer names. Design an integration strategy that minimises false matches and preserves auditability.
3. After a merge, total revenue is 18% higher than before the merge. Identify the most likely causes, describe diagnostic tests in Power Query, and recommend corrective action.

Multiple Choice Questions with Answers

Question 1

Which operation combines records by placing rows from one table beneath rows from another?

A. Merge

B. Append

C. Expand

D. Group By

Correct Answer: B. Append

Question 2

Which join retains every row from the left table and adds matching columns from the right table?

A. Inner join

B. Left outer join

C. Left anti join

D. Full outer join

Correct Answer: B. Left outer join

Question 3

What is the main purpose of a left anti join?

A. To return matched rows from both tables

B. To retain all rows from the right table

C. To identify left-table rows without a match

D. To aggregate duplicate keys

Correct Answer: C. To identify left-table rows without a match

Question 4

A product master is intended to contain one row per ProductCode. Which condition represents uniqueness?

A. f(k) = 0 for every key

B. f(k) ≤ 1 for every key

C. f(k) ≥ 2 for every key

D. f(k) = 100 for every key

Correct Answer: B. f(k) ≤ 1 for every key

Question 5

Why might a merge unexpectedly increase the number of rows?

A. The source columns are sorted

B. The lookup key has multiple matching rows

C. The query uses a left anti join

D. The tables contain compatible data types

Correct Answer: B. The lookup key has multiple matching rows

Question 6

Which join is generally most suitable for reconciling all records from two systems?

A. Full outer join

B. Inner join

C. Left anti join

D. Right outer join only

Correct Answer: A. Full outer join

Question 7

Which is usually the strongest matching key?

A. Customer display name

B. Uncontrolled free-text address

C. Stable system-generated CustomerID

D. Row number assigned during refresh

Correct Answer: C. Stable system-generated CustomerID

Question 8

What should usually be done before appending tables whose equivalent fields have different names?

A. Delete both fields

B. Rename them consistently

C. Convert every field to text without review

D. Use an inner join

Correct Answer: B. Rename them consistently

Question 9

If every sales row must remain available even when its product is missing from the master, which join should be used?

A. Left outer join

B. Inner join

C. Left anti join

D. Right anti join

Correct Answer: A. Left outer join

Question 10

Which practice best supports auditability in a reconciliation process?

A. Discarding source identifiers after the merge

B. Keeping source identifiers and transformation status

C. Removing all unmatched records

D. Replacing all source values with labels

Correct Answer: B. Keeping source identifiers and transformation status