Combining Data with Merge and Append

Introduction

Business intelligence rarely begins with one perfect table. Sales may be stored in monthly files, customer information in a database, targets in spreadsheets, and product attributes in a separate system. Power Query provides two fundamental ways to combine these structures: merge, which joins columns from related tables, and append, which stacks rows from tables with a similar structure.

These operations are conceptually related to relational database operations. A merge is comparable to a join, while an append is comparable to a union. Correct use requires more than selecting a command: the analyst must understand table grain, keys, data types, missing values, duplicate records, column alignment, and validation.

This chapter explains how to integrate tables in Power Query, choose suitable join types, resolve key mismatches, consolidate recurring files, and validate the results.

Learning Objectives

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

• distinguish between merge and append operations;
• explain the role of primary keys, foreign keys, and table grain;
• select an appropriate join type for a business requirement;
• prepare and standardize keys before merging;
• append tables from different periods or sources;
• combine recurring files from a folder;
• identify duplicate, unmatched, and misaligned records;
• validate combined data using systematic checks; and
• apply governance and quality controls to integrated Power BI data.

1. The Conceptual Difference Between Merge and Append

1.1 Merge: combining columns through a relationship

A merge combines two tables horizontally. It matches rows according to one or more selected columns and brings columns from the second table into the first table.

For example, a sales table may contain:

A product table may contain:

Merging on ProductCode adds ProductName and Category to the sales rows. The operation depends on a meaningful relationship between the selected columns.

1.2 Append: combining rows with a common structure

An append combines tables vertically. It places the rows of one table below the rows of another. For example, January and February sales tables can be appended into one sales table.

Appending is appropriate when tables represent the same type of event or entity, even if they originate in different files or periods.

1.3 A practical decision rule

Ask the following question:

• Do I need additional columns describing existing rows? Use Merge.
• Do I need additional rows representing the same type of record? Use Append.

Confusing these operations can produce incorrect models. For example, appending a product lookup table to a sales table does not create a product description for each sale; it creates a mixed table with incompatible row meanings.

2. Data Grain and Relational Keys

Before combining tables, identify the grain of each table. Grain means the level represented by one row.

Examples include:

• one row per customer;
• one row per product;
• one row per invoice;
• one row per invoice line;
• one row per employee per month.

Grain is important because a merge can change the number of rows if the matching column is not unique in the lookup table.

2.1 Primary and foreign keys

A primary key uniquely identifies a row in a table. A foreign key is a column containing values that refer to a key in another table.

For example:

• Product[ProductCode] may be a primary key;
• Sales[ProductCode] may be a foreign key.

A reliable lookup table normally contains one row per key. If ProductCode P01 appears three times in the product table, merging it with one sales row may produce three output rows. This can inflate quantities and sales when the result is later aggregated.

2.2 One-to-one and one-to-many relationships

A merge is often used in a one-to-many context. One product can appear in many sales rows, but each product should appear once in the product lookup table. The lookup side must generally be unique for safe attribute expansion.

A many-to-many merge may be valid in some situations, but it must be intentional. For example, matching transactions to overlapping promotional periods can legitimately create multiple matches. Such cases require explicit business rules and careful validation.

3. Merge Operations in Power Query

In Power Query, select a query and choose Home > Merge Queries. Select the second query, select corresponding columns, choose the join kind, and confirm. Power Query creates a new column containing matching rows from the second table. The column is usually expanded to select the required fields.

3.1 Selecting join columns

Join columns should:

1. represent the same business concept;
2. have compatible data types;
3. use comparable formatting;
4. have the same level of detail; and
5. be sufficiently complete and reliable.

A customer identifier should not be matched to a customer name unless names are governed and guaranteed to be unique. Names may differ in spelling, punctuation, capitalization, or language.

For a composite key, select multiple columns in the same order in both tables. For example, a budget may be identified by the combination of Department and FinancialYear. Department alone may not be unique.

3.2 Join types

Power Query supports several join kinds. Their business meaning is summarized below.

#### Left outer join

A left outer join is the most common choice for enriching a fact or transaction table. It preserves every row in the first table. If no match exists, the expanded columns contain null values.

#### Inner join

An inner join removes unmatched rows. This can be appropriate when only fully validated records should proceed, but it can also hide data-quality problems. It should not be used merely to eliminate nulls without investigating their cause.

#### Full outer join

A full outer join is useful for reconciliation. It reveals records existing only in the first table, only in the second table, or in both.

#### Anti joins

Anti joins are valuable diagnostic tools. A left anti join between Sales and Products identifies sales keys that have no corresponding product. A right anti join can identify products that have never appeared in sales.

4. Handling Key Mismatches

A failed match does not necessarily mean that a record is absent. Often, the values differ technically even though they refer to the same entity.

Common causes include:

• leading or trailing spaces;
• inconsistent capitalization;
• punctuation differences;
• hidden non-printing characters;
• numeric values stored as text;
• different date formats;
• leading zeros being removed;
• inconsistent prefixes or suffixes; and
• spelling or coding changes between systems.

4.1 Standardization techniques

Power Query transformations that commonly improve matching include:

• Trim: removes leading and trailing spaces;
• Clean: removes many non-printing characters;
• Uppercase or Lowercase: standardizes letter case;
• Replace Values: corrects known coding differences;
• Change Type: aligns numeric, text, date, or logical types;
• Extract or Split Columns: isolates a common identifier; and
• Merge Columns: constructs a composite key.

A robust process preserves the original key and creates a standardized key for matching. This supports auditability.

For example, if customer codes contain unwanted spaces, create a transformed value conceptually equivalent to:

Text.Upper(Text.Trim([CustomerCode]))

The exact expression should be adapted to the source data. Standardization must not destroy meaningful distinctions. Converting all values to uppercase is usually safe for identifiers, but removing punctuation may be unsafe if punctuation differentiates valid codes.

4.2 Data types and dates

The value 00125 stored as text is not necessarily equivalent to the number 125. If leading zeros are part of the identifier, the key should remain text in both tables. Similarly, dates should be converted to a common date type rather than matched as differently formatted text.

4.3 Fuzzy matching

Power Query can support fuzzy matching for selected text joins. Fuzzy matching is useful for approximate names, addresses, or descriptions, but it is not a substitute for a controlled master-data key. It may create false matches, especially where names are similar.

Use fuzzy matching only when:

• the business meaning of similarity is clear;
• the matching threshold is tested;
• ambiguous matches are reviewed; and
• the original values and match confidence are retained where possible.

5. Expanding a Merged Column

After a merge, the new column contains nested tables. Select the expand icon to choose columns from the related table.

Expansion should be selective. Bringing every column from a large lookup table can increase model complexity, refresh time, and ambiguity. Select only the attributes needed for analysis or reporting.

If a lookup key is duplicated, expansion may produce multiple rows. Therefore, inspect the lookup table before merging. A useful diagnostic is to group by the proposed key and count rows. Any count greater than one requires explanation.

6. Append Operations in Power Query

Choose Home > Append Queries to combine tables vertically. Power Query aligns columns by column name, not simply by physical position. If one table has a column that another lacks, the missing cells are filled with null values.

6.1 Append two tables versus three or more tables

Appending two tables is suitable for a one-off combination. The option for three or more tables is useful when several prepared queries must be combined. For recurring data, however, a folder-based approach is usually more maintainable than manually adding every new query.

6.2 Column alignment and schema drift

Suppose January contains Quantity and NetSales, while February contains Quantity, NetSales, and Discount. The appended result contains all three columns. January rows have null in Discount.

This behavior is useful but can conceal schema problems. A renamed column, such as Net Sales instead of NetSales, becomes a separate column. The result may refresh successfully while producing incomplete analysis.

Before appending, compare:

• column names;
• data types;
• units and definitions;
• required and optional fields; and
• row grain.

6.3 Adding source and period metadata

When files are appended, retain metadata such as:

• source file name;
• reporting period;
• source system;
• import date; and
• business unit.

These fields support traceability and make it possible to identify unusual records after combination.

7. Combining Recurring Files from a Folder

A common business requirement is to consolidate files received periodically. Examples include monthly sales workbooks, weekly inventory extracts, and daily service logs.

A typical Power Query workflow is:

1. Place files with a consistent structure in a controlled folder.
2. Connect to the folder using the Folder connector.
3. Filter out temporary files, archive files, and unrelated documents.
4. Inspect file names, extensions, dates, and sizes.
5. Invoke a transformation function for each file.
6. Combine the transformed tables.
7. retain source-file metadata.
8. validate row counts and totals.

The folder-combine experience generally creates a sample-file transformation and applies it to each file. Changes should be made in the transformation query or function so that future files receive the same treatment.

7.1 Designing a stable folder process

A stable process should define:

• an expected file naming convention;
• required columns;
• acceptable file types;
• treatment of empty files;
• treatment of duplicate files; and
• an archival or retention policy.

If a file contains a different header row, an extra title, or a changed sheet name, the transformation may fail or produce incorrect results. Such conditions should be detected rather than silently ignored.

7.2 File-level error handling

A production process should make failed files visible. A useful design retains a file-status table containing file name, processing status, error message, and row count. This enables an analyst to distinguish “no records” from “file failed to process.”

8. Validation of Combined Results

Combining data is incomplete until the result has been validated. Validation should occur at several levels.

8.1 Structural validation

Check that:

• expected queries exist;
• required columns are present;
• column names are correct;
• data types are appropriate;
• no unexpected columns have appeared; and
• append sources have compatible grain.

8.2 Row-count validation

For an append, compare the output row count with the sum of input row counts, allowing for deliberately filtered rows. If input tables contain row counts r₁, r₂, …, rₙ and no rows are filtered or removed, the expected append count is:

Here, R is the expected combined row count and rᵢ is the row count of input table i. The relationship assumes that every input row is appended once and that no transformation removes or duplicates rows.

For a merge, row-count behavior depends on key uniqueness. A left outer merge should preserve the left-table row count when the right-side key is unique. If the row count increases, investigate duplicate matches.

8.3 Completeness validation

Calculate the number and proportion of unmatched keys. If N is the number of rows in the left table and U is the number of rows without a match, the unmatched proportion is:

where p is a proportion and N must be greater than zero. The result may be expressed as a percentage by multiplying p by 100. This measure is meaningful only when the definition of “unmatched” is consistent and null or intentionally blank keys are treated appropriately.

8.4 Duplicate validation

Group by the intended key and count rows. Duplicates may indicate:

• repeated source records;
• a non-unique lookup table;
• a key that is too narrow;
• a legitimate one-to-many relationship; or
• an accidental many-to-many merge.

8.5 Financial and operational reconciliation

Where possible, compare totals before and after combination. For example, total sales by month should reconcile to the source reports. A reconciliation difference can be expressed as:

where D is the difference, T_combined is the total in the integrated result, and T_source is the independently verified source total. A difference of zero is expected only when the same filters, units, currency, and inclusion rules apply.

8.6 Exception reporting

Create diagnostic queries for:

• unmatched sales keys;
• duplicate product keys;
• invalid dates;
• null mandatory fields;
• unexpected file structures; and
• records outside the reporting period.

Diagnostic queries should be retained as part of the solution rather than deleted after development.

9. Worked Example: Product Enrichment and Monthly Consolidation

A retailer receives one sales file per month. Each file contains SaleID, ProductCode, Quantity, and NetSales. A product master table contains ProductCode, ProductName, Category, and StandardCost.

Step 1: Append the monthly files

The analyst connects to the folder, filters for approved CSV files, applies a common transformation, and appends the files. A SourceFile column is retained. The resulting table has one row per sales line.

Step 2: Standardize the product key

Both the sales and product tables are converted to text for ProductCode. Trim and uppercase transformations are applied. Leading zeros are preserved because product codes are identifiers rather than quantities.

Step 3: Check product-key uniqueness

The product table is grouped by ProductCode. If P01 appears once, it is suitable as a lookup key. If P01 appears twice because of two effective dates, the analyst must decide whether the merge requires a current product record or a date-effective match.

Step 4: Merge sales with products

A left outer merge is selected because every sales row must remain visible, including sales with an invalid or missing product code. ProductName and Category are expanded.

Step 5: Investigate unmatched rows

A left anti join produces unmatched product codes. The analyst discovers that some source files use “P-01” while the product master uses “P01”. After confirming that the difference is a formatting defect, a controlled replacement rule is applied and the merge is repeated.

Step 6: Validate

The analyst checks that:

• the appended row count equals the sum of accepted monthly rows;
• the sales total agrees with the source reports;
• the merge did not increase the number of sales rows;
• unmatched product codes are zero or documented; and
• product keys remain unique.

The final result supports analysis by month, product, and category without losing transactions or double-counting sales.

10. Applied Case: Regional Service Data

This is an illustrative case.

Background

A service organization receives weekly Excel files from four regions. Each file records service tickets. Regional managers want a single Power BI report showing ticket volume, response time, priority, and customer segment.

Problem

The files appear similar but contain several inconsistencies. One region labels the identifier Ticket No, another uses TicketID. Dates are stored in different formats. Customer codes in one region contain trailing spaces. In addition, the customer master includes customers who have not opened tickets.

Analysis

The intended grain is one row per service ticket. The weekly files should therefore be appended. Customer Segment is an attribute of the customer and should be brought in through a merge. The customer master should contain one row per CustomerCode.

Approach and solution

The analyst creates a folder query and a transformation function. Column names are standardized, dates are converted to a common date type, and CustomerCode is trimmed and uppercased. A SourceRegion column is derived from the file name.

The weekly files are appended. The customer master is checked for duplicate CustomerCode values. A left outer merge brings Customer Segment into the ticket table. Anti-join diagnostics identify unmatched customers. A full outer reconciliation between the customer list and ticket keys identifies customers with no tickets and tickets with invalid customer codes.

Outcome

The resulting model preserves every valid service ticket, identifies data-quality exceptions, and supports regional and customer-segment analysis. Managers can distinguish a genuine absence of service activity from a missing customer match.

Learning points

1. Append is used for repeated observations with the same grain.
2. Merge is used to enrich observations with descriptive attributes.
3. Metadata such as region and file name improves traceability.
4. Anti joins are practical tools for exception management.
5. Validation must include both totals and relationship quality.

11. Governance, Ethics, and Security

Data combination can create governance risks even when the technical operation is correct.

11.1 Data lineage

Document the source of each query, transformation steps, merge keys, append sources, and refresh frequency. Source-file metadata supports audit and incident investigation.

11.2 Access control

Combining tables can unintentionally expose sensitive information. Customer, employee, health, or financial attributes should be included only when necessary. Apply appropriate access controls and Power BI row-level security where required.

11.3 Personal data minimization

Do not append or merge personal information merely because it is available. Retain the minimum fields required for the stated analytical purpose. Consider retention periods and applicable privacy requirements.

11.4 Bias and data quality

If one region submits data more completely than another, combined reporting may make regional performance appear different for reasons unrelated to actual service quality. Analysts should report coverage and data-quality indicators alongside performance measures.

12. Best Practices and Limitations

Best practices

• Define table grain before combining data.
• Use stable business keys rather than names where possible.
• Preserve original values and create standardized matching columns.
• Verify uniqueness on the lookup side of a merge.
• Prefer folder-based combination for recurring files.
• Keep source and period metadata.
• Use anti joins to investigate unmatched records.
• Validate row counts, totals, data types, and duplicates.
• Keep diagnostic queries in the solution.
• Document assumptions and exceptions.

Limitations

Power Query transformations do not automatically resolve ambiguous business identities. Fuzzy matching can produce false positives. Folder processes depend on consistent file structures. A technically successful refresh does not prove that the business result is correct. Moreover, combining data can amplify errors: a duplicated lookup key may multiply transaction rows, while an inner join may silently remove important records.

For large or highly governed environments, data integration may be better performed in a database, data warehouse, or dataflow before Power BI consumption. Power Query remains valuable for self-service preparation, prototyping, and controlled transformation.

Contemporary Practice: Automation and AI Assistance

Modern data platforms increasingly provide profiling, schema-detection, and assisted transformation features. These can suggest data types, identify anomalies, or generate transformation expressions. Such assistance should be treated as a productivity aid rather than an authority. The analyst remains responsible for confirming key definitions, match logic, privacy implications, and validation results.

An AI-generated transformation may be syntactically valid but conceptually wrong—for example, converting an identifier to a number and thereby removing leading zeros. Human review and test cases remain essential.

Chapter Summary

Merge and append solve different integration problems. Merge joins related tables and adds columns; append stacks tables and adds rows. Reliable integration depends on understanding table grain, selecting suitable keys, aligning data types, and choosing a join kind that reflects the business requirement.

Left outer joins commonly enrich transaction tables while preserving all transactions. Inner joins retain only matched records, full outer joins support reconciliation, and anti joins expose missing or unused keys. Key standardization can resolve formatting differences, but fuzzy matching should be controlled and reviewed.

Append operations are appropriate for recurring files with a common row meaning. Folder-based solutions improve maintainability, especially when source-file metadata and error handling are retained. Validation should include structural checks, row counts, totals, duplicate analysis, unmatched-key analysis, and reconciliation against trusted sources. Governance requires lineage, access control, privacy protection, and awareness that technical success does not guarantee analytical validity.

Key Terms

Merge: A Power Query operation that joins two tables and adds columns based on matching values.

Append: A Power Query operation that combines tables vertically by adding rows.

Join: A relational operation that connects rows from two tables using one or more matching columns.

Union: A relational operation that combines rows from compatible tables.

Table grain: The level of detail represented by one row in a table.

Primary key: A column or set of columns that uniquely identifies a row.

Foreign key: A value that refers to a key in another table.

Left outer join: A join retaining every row from the first table and matching rows from the second.

Inner join: A join retaining only rows that match in both tables.

Full outer join: A join retaining matched and unmatched rows from both tables.

Anti join: A join used to find rows without a match in another table.

Composite key: A key formed from two or more columns.

Lookup table: A table containing descriptive attributes associated with business keys.

Schema drift: An unexpected change in columns, names, types, or structure over time.

Fuzzy matching: Approximate text matching based on similarity rather than exact equality.

Data lineage: Documentation of data origin, transformations, and movement.

Reconciliation: Comparison of integrated results with an independent or trusted source.

Referential integrity: The condition that foreign-key values correspond appropriately to valid keys.

Review Questions

5 Short Answer Questions

1. What is the main difference between a merge and an append?
2. Why is table grain important before combining tables?
3. What happens to unmatched rows in a left outer join?
4. Give two common causes of key mismatches.
5. Why should source-file metadata be retained when appending files?

5 Long Answer Questions

1. Explain the major Power Query join types and provide a business use case for each.
2. Describe a systematic process for preparing keys before a merge.
3. Explain how a folder-based Power Query solution can consolidate recurring files.
4. Discuss why duplicate lookup keys can create incorrect analytical results.
5. Describe a comprehensive validation framework for a combined dataset.

3 Analytical / Application Questions

1. A sales table has 100,000 rows. After a left merge with a product table, it has 103,500 rows. What possible causes should be investigated, and which diagnostic steps would you perform?
2. Four monthly files contain 12,000, 11,500, 13,000, and 12,500 accepted rows. The appended table contains 47,800 rows. Analyze the discrepancy and propose a validation procedure.
3. A customer master contains customers with no transactions, while the transaction table contains several invalid customer codes. Which merge or anti-join techniques would you use to report both situations without deleting valid transactions?

Multiple Choice Questions with Answers

Question 1

Which operation should be used to add ProductName to existing sales rows?

A. Append

B. Merge

C. Transpose

D. Group By

Correct Answer: B. Merge

Question 2

Which join retains every row from the first table, including rows with no match?

A. Inner join

B. Full outer join

C. Left outer join

D. Left anti join

Correct Answer: C. Left outer join

Question 3

What is the primary risk of duplicate keys in the lookup table during a merge?

A. Columns are automatically deleted

B. Rows may be multiplied

C. All data types become text

D. The query becomes an append

Correct Answer: B. Rows may be multiplied

Question 4

Which operation is most appropriate for combining January and February sales tables with the same grain?

A. Append

B. Merge

C. Fuzzy match

D. Unpivot

Correct Answer: A. Append

Question 5

Which join identifies rows in Sales that have no corresponding Product record?

A. Left anti join

B. Inner join

C. Right outer join

D. Full outer join

Correct Answer: A. Left anti join

Question 6

Why should an identifier such as 00125 often be stored as text?

A. Text values always use less memory

B. Text prevents all duplicates

C. Leading zeros may be meaningful

D. Numbers cannot be merged

Correct Answer: C. Leading zeros may be meaningful

Question 7

Four source tables contain 10, 20, 30, and 40 rows, with no filtering or duplication. What row count should their append produce?

A. 40

B. 60

C. 90

D. 100

Correct Answer: D. 100

Question 8

Which feature is generally most suitable for consolidating recurring files in a controlled folder?

A. Folder connector with a common transformation

B. Manual copy and paste into a report

C. Inner join between file names

D. Deleting file metadata before import

Correct Answer: A. Folder connector with a common transformation

Question 9

What is the main purpose of a full outer join in data integration?

A. To retain only matched records

B. To enrich only the first table

C. To compare matched and unmatched records from both tables

D. To remove duplicate columns

Correct Answer: C. To compare matched and unmatched records from both tables

Question 10

Which practice best supports auditability when standardizing keys?

A. Overwrite the original key immediately

B. Preserve the original and create a standardized key

C. Convert every identifier to a number

D. Use fuzzy matching without review

Correct Answer: B. Preserve the original and create a standardized key