Merge and Append Queries
Learn how to combine data from multiple tables using Merge Queries and Append Queries in Power Query Editor. Understand join types, matching columns, schema alignment, and common data combination errors.
1. Introduction to Merge and Append
Business data is often stored in multiple tables or files. Power Query provides two important operations for combining data: Merge Queries and Append Queries.
Merge Queries
Merge combines tables horizontally by matching values in one or more selected columns. It is commonly used when two tables contain related information about the same customers, products, orders, or other business entities.
Append Queries
Append combines tables vertically by adding rows from one table below the rows of another table. It is useful when datasets have the same or similar structure, such as monthly sales files.
Visual 1: Merge adds related columns; Append adds rows from another table.
2. Understanding Merge Queries
Merge Queries combines two tables using matching column values. The selected columns act as keys for finding related rows. The selected join type determines which rows are retained.
Example: Orders and Customers
Orders table
| OrderID | CustomerID | Amount |
|---|---|---|
| 101 | C01 | 2500 |
| 102 | C02 | 1800 |
| 103 | C01 | 3200 |
Customers table
| CustomerID | CustomerName | City |
|---|---|---|
| C01 | Samiksha | Pune |
| C02 | Omkar | Mumbai |
| C03 | Pradnya | Nashik |
Both tables contain CustomerID. This shared field can be used to match each order to its customer details.
3. Steps to Merge Queries
- Open Power BI Desktop and select Home → Transform Data.
- Select the primary query, such as Orders.
- Open the Home tab in Power Query Editor.
- Select Merge Queries or Merge Queries as New.
- Select the second table, such as Customers.
- Select CustomerID in the first table and the corresponding CustomerID in the second table.
- Choose the required join kind.
- Confirm the merge operation.
- Use the expand icon in the resulting nested table column.
- Select the customer fields to include, such as CustomerName and City.
- Review the result and rename the query or columns if needed.
Visual 2: A typical merge workflow in Power Query Editor.
4. Understanding Join Types
The join kind determines which records are retained when Power Query matches two tables. Choose the join according to the business question and the data that must be preserved.
| Join Kind | What It Returns | Typical Use |
|---|---|---|
| Left Outer | All rows from the first table and matching rows from the second. | Keep every order and add customer details where available. |
| Right Outer | All rows from the second table and matching rows from the first. | Keep every customer from the second table. |
| Full Outer | All rows from both tables, with matches combined. | Identify records from either source, including unmatched records. |
| Inner | Only rows that have matching keys in both tables. | Keep only orders with matching customer records. |
| Left Anti | Rows from the first table with no match in the second. | Find orders whose customer key is missing from Customers. |
| Right Anti | Rows from the second table with no match in the first. | Find customers with no corresponding order record. |
Visual 3: Join types control which matched and unmatched records are retained.
5. Expanding Merged Columns
After merging, Power Query typically adds a column containing nested tables from the second query. Expand that column to select the fields you want to add to the result.
Example Result After a Left Outer Merge
| OrderID | CustomerID | Amount | CustomerName | City |
|---|---|---|---|---|
| 101 | C01 | 2500 | Samiksha | Pune |
| 102 | C02 | 1800 | Omkar | Mumbai |
| 103 | C01 | 3200 | Samiksha | Pune |
CustomerName and City are added from the Customers table. Customer C01 appears twice because two orders belong to that customer.
- Locate the merged table column.
- Select the expand icon in the column heading.
- Choose only the fields needed for the report.
- Decide whether to keep or remove the original column-name prefix.
- Confirm and inspect the resulting table.
6. Understanding Append Queries
Append Queries adds rows from one query to another. It is useful when datasets represent the same type of information for different periods, branches, locations, or business units.
Example: Monthly Sales
JanuarySales
| OrderID | Product | Amount |
|---|---|---|
| 101 | Laptop | 50000 |
| 102 | Monitor | 12000 |
FebruarySales
| OrderID | Product | Amount |
|---|---|---|
| 103 | Keyboard | 2500 |
| 104 | Mouse | 900 |
Visual 4: Appending monthly sales queries produces one longer sales table.
7. Steps to Append Queries
- Open Power Query Editor.
- Select Home → Append Queries or Append Queries as New.
- Choose whether to append two tables or three or more tables.
- Select the required queries.
- Confirm the operation.
- Review the resulting rows and columns.
- Check data types and values in the combined query.
- Rename the result to a meaningful name, such as CombinedSales.
What Happens When Column Names Differ?
Append aligns columns by column name. If one query contains Product and another contains ProductName, Power Query may create separate columns rather than recognizing them as the same field. Values that do not exist in a particular source may appear as null.
8. Merge vs. Append: Key Differences
| Feature | Merge Queries | Append Queries |
|---|---|---|
| Direction | Combines related information horizontally. | Combines records vertically. |
| Primary purpose | Match related rows between tables. | Add rows from one dataset to another. |
| Key requirement | Usually uses one or more matching columns. | Does not require matching keys. |
| Common example | Add customer details to orders. | Combine January and February sales. |
| Typical result | Additional columns after expansion. | Additional rows in the combined table. |
Visual 5: Use the desired result to decide whether to merge or append.
9. Common Errors and Troubleshooting
Problem: Merge Produces Null Values
Possible causes include mismatched key values, leading or trailing spaces, different data types, or missing records. Compare the selected keys and standardize them before merging.
Problem: Merge Produces More Rows Than Expected
The matching key may occur multiple times in the second table. Check key uniqueness and decide whether duplicates are valid before expanding the merged column.
Problem: Append Creates Extra Columns
Column names may differ between the source queries. Standardize the schema and ensure that equivalent fields use consistent names.
Problem: Values Become Null After Append
A field may be missing from one source, or its name may differ. Inspect each input query and align the column names.
Problem: Data Type Errors Appear
Sources may contain different formats or invalid values. Check data types, locale settings, and conversion errors before loading.
10. Practical Exercise
Exercise A: Merge Orders with Customers
- Import an Orders table containing OrderID, CustomerID, and Amount.
- Import a Customers table containing CustomerID, CustomerName, and City.
- Open Power Query Editor.
- Merge Orders with Customers using CustomerID.
- Select Left Outer as the join kind.
- Expand CustomerName and City.
- Inspect the result for unmatched customer IDs.
Exercise B: Append Monthly Sales
- Import JanuarySales and FebruarySales.
- Ensure both queries have compatible column names and data types.
- Use Append Queries as New.
- Select both monthly queries.
- Name the new query CombinedSales.
- Verify that all expected rows are present.
Expected outcome: One query enriched with customer details and another query containing combined monthly sales records.
11. Knowledge Check
Question 1: Which operation combines related columns from two tables?
A. Append Queries
B. Merge Queries
C. Remove Rows
D. Change Type
Question 2: Which operation is suitable for combining January and February sales rows?
A. Merge Queries
B. Replace Values
C. Append Queries
D. Split Column
Question 3: What does a Left Outer join retain?
A. Only unmatched right rows
B. All left rows and matching right rows
C. Only matching rows
D. Only unmatched left rows
Question 4: Why can a merge create extra rows after expansion?
A. The report theme changed
B. The matching key appears multiple times in the second table
C. The query was renamed
D. The column font changed
Question 5: How does Append generally align columns?
A. By column name
B. By cell color
C. By report page order
D. By visual position
View Answers
- B — Merge Queries.
- C — Append Queries.
- B — All left rows and matching right rows.
- B — The matching key appears multiple times in the second table.
- A — By column name.
Summary
In this lesson, you learned how Merge Queries combines related tables using matching columns and how join types control the inclusion of matched and unmatched records.
You also learned how Append Queries combines rows from multiple datasets, why consistent column names and data types matter, and how to troubleshoot common data combination problems.