This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Prepare the data (25–30%)
   --> Transform and load the data
      --> Merge and append queries

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

---

Combining data from multiple sources or tables is a common requirement in real-world analytics. In Power Query, you accomplish this using two primary operations: Merge and Append. Understanding when and how to use each — and the impact they have on your data model — is essential for the PL-300 exam.

---

What Are “Merge” and “Append”?

Merge Queries

A merge operation combines two tables side-by-side based on matching values in one or more key columns — similar to SQL joins.

Think of it as a join:

• Inner join
• Left outer join
• Right outer join
• Full outer join
• Anti joins
• Etc.

Merge is used when you want to enrich a table with data from another table based on a common identifier.

---

Append Queries

An append operation stacks tables top-to-bottom, effectively combining rows from multiple tables with the same or similar structure.

Think of it as UNION:

• Append two tables
• Append three or more (chain append)
• Works best when tables have similar columns

Append is used when you want to combine multiple datasets that share the same business structure (e.g., quarterly sales tables).

---

Power Query as the Correct Environment

Both merge and append operations are done in the Power Query Editor (before loading data into the model).

This means:

• You shape data before modeling
• You reduce model complexity
• You avoid extra DAX calculations

Exam tip: The exam tests when to use merge vs append, not just how.

---

When to Use Append

Use Append when you have:

• Multiple tables with the same columns and business meaning
• Data split by time period or region (e.g., Jan, Feb, Mar)
• A long “flat” dataset that you want to combine into one super-table

Scenario Example

You receive separate sales tables for each month. To analyze sales for the year, you append them into one dataset.

---

When to Use Merge

Use Merge when you need to:

• Bring additional attributes into a table
• Look up descriptive information
• Combine facts with descriptive dimensions

Scenario Example

You have a fact table with ProductID and a product lookup table with ProductID and ProductName. You need to add ProductName to the fact table.

---

Types of Joins (Merge)

In Power Query, Merge supports multiple join types. Understanding them is often tested in PL-300 scenarios:

Join Type	What It Returns	Typical Use Case
Left Outer	All rows from left + matching from right	Enrich main table
Right Outer	All rows from right + matching from left	Less common
Inner	Only matching rows	Intersection of datasets
Full Outer	All rows from both tables	When you don’t want to lose any rows
Anti Joins	Rows that don’t match	Data quality or missing keys

Exam Insight: The answer is often Left Outer for common enrichment scenarios.

---

Column Mismatch and Transform

Append Considerations

• Column names and types should ideally match
• Mismatched columns will still append, but will fill blanks where values don’t align
• After appending, you may need to:
  • Reorder columns
  • Rename columns
  • Change data types

Merge Considerations

• Keys must be of the same data type
• If datatype mismatches exist (e.g., text vs number), the join may fail
• After merging, you may need to:
  • Expand the new table
  • Select only needed columns
  • Rename expanded fields

---

Performance and Model Impact

Append Impacts

• Combined table may be significantly larger
• May improve performance if multiple small tables are consolidated
• Avoids repetitive DAX measures

Merge Impacts

• Adds columns and enriches tables
• Can increase column cardinality
• May require careful relationships after load

---

Differences Between Merge and Append

Aspect	Merge	Append
Structure	Side-by-side	Top-to-bottom
Use Case	Enrichment / lookup	Stacking similar tables
Similar to	SQL Join	SQL UNION
Requires key matching	Yes	Optional
Best for disparate data	Yes	Only if structures align

---

Common Mistakes (Often Tested)

❌ Appending tables with wildly different structures

This results in extra null columns and a messy model.

---

❌ Merging on non-unique keys

Leads to duplication or unexpected rows.

---

❌ Forgetting to expand merged columns

After merge, you must expand the related table to pull in needed fields.

---

❌ Ignoring data types

Merges fail silently if keys are not the same type (text vs number).

---

Best Practices for PL-300 Candidates

• Append only when tables represent the same kind of data
• Merge when relating lookup/detail information
• Validate column data types before merging
• Clean and remove unnecessary columns before append/merge
• Rename and reorder columns for clarity
• Use descriptive steps and comments for maintainability

---

How This Appears on the PL-300 Exam

The exam often presents scenarios like:

You need to combine multiple regional sales tables into one dataset. Which transformation should you use?

Correct thought process: The tables have the same columns → Append

---

You need to add product details to a sales table based on product ID. What do you do?

Correct thought process: Combine tables on common key → Merge

---

Quick Decision Guide

Scenario	Recommended Transformation
Combine tables with same fields	Append
Add lookup information to a table	Merge
Create full dataset for modeling	Append first
Add descriptive columns	Merge next

---

Final PL-300 Takeaways

• Append = stack tables (same structure)
• Merge = combine tables (key relationship)
• Always check data type compatibility
• Transform before load improves model clarity
• Merge/Appending decisions are often scenario-based

---

Practice Questions

Go to the Practice Exam Questions for this topic.