Merge Queries, Append Queries, and Relationship Keys

Quick Answer: Merge = horizontal join (adds columns from another table based on matching keys). Append = vertical stack (adds rows from another table with the same structure). Use Left Outer Join when you want all rows from the first table plus matching data from the second. Always verify that key columns have the same data type before merging.

Merge Queries

Merge Queries combines two tables horizontally by matching rows based on one or more key columns. This is equivalent to a SQL JOIN.

Home tab → Merge Queries (or Merge Queries as New)

Join Types

Merge Process

1. Select the first (left) table
2. Select the matching column(s) in the first table
3. Select the second (right) table
4. Select the matching column(s) in the second table
5. Choose the join type
6. Click OK → a new column with nested tables appears
7. Expand the nested column to select which fields to include

Merge Queries vs. Merge Queries as New

Best Practice: Use "Merge Queries as New" to keep original queries unchanged and create a clean combined query.

Merge Gotchas

• Column data types must match between the two tables
• Case sensitivity matters for text matches (use Lowercase/Uppercase transform first)
• Trailing spaces can cause false non-matches (use Trim first)
• Multi-column matches are supported (hold Ctrl to select multiple columns)

Append Queries

Append Queries stacks tables vertically — combining rows from two or more tables with similar structures.

Home tab → Append Queries (or Append Queries as New)

Append Options

How Append Works

• Columns are matched by name (not position)
• If a column exists in one table but not the other, nulls are added
• Column data types from the first table take precedence
• Row order: first table's rows, then second table's rows

Example: Appending monthly sales tables:

Table: JanSales + Table: FebSales → Combined: AllSales

Append vs. Merge

Reference vs. Duplicate Queries

Reference Query

Creates a pointer to an existing query — changes to the source query propagate to the reference:

Right-click query → Reference

• Uses the output of the source query as its starting point
• Maintains a dependency (changes flow through)
• Does not duplicate the data transformation work
• Ideal for creating different views of the same base data

Duplicate Query

Creates an independent copy of the query — changes to the source do NOT propagate:

Right-click query → Duplicate

• Copies all steps from the source query
• No dependency — completely independent
• Changes to one query do not affect the other
• Ideal when you need a starting point but will diverge significantly

When to Use Each

Creating Appropriate Relationship Keys

For the data model, relationships require properly formatted keys:

Key Requirements

• Unique values on at least one side (the "one" side of a one-to-many)
• Same data type in both columns
• No null values in the key column (nulls don't match)
• Consistent formatting (same case, no extra spaces)

Creating Composite Keys

When no single column uniquely identifies rows, create a composite key:

// In Power Query, combine columns
Table.AddColumn(previousStep, "CompositeKey",
    each [StoreID] & "-" & [ProductID])

Creating Surrogate Keys

When natural keys are unreliable, add an index column:

Add Column → Index Column → From 1

Configuring Data Loading

Control which queries are loaded into the data model:

Best Practice: Disable loading for intermediate/staging queries that exist only to feed other queries. This reduces model size and refresh time.

On the Exam

The PL-300 frequently tests:

• Choosing the correct join type for a Merge scenario
• Understanding the difference between Merge and Append
• Knowing when to use Reference vs. Duplicate queries
• Creating composite keys for relationships
• Configuring which queries load into the data model