ExamFinch.com My Study

Study guide

3.3 Merge Queries, Append Queries, and Relationship Keys

Practice this exam

Microsoft Power BI Data Analyst PL-300

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the PL-300 Exam The Power BI Ecosystem

2 Chapter 1: Get or Connect to Data

1.1 Data Source Types and Connections 1.2 Storage Modes: Import, DirectQuery, and Direct Lake 1.3 Data Source Settings and Parameters

3 Chapter 2: Profile and Clean the Data

2.1 Data Profiling in Power Query 2.2 Resolving Errors, Nulls, and Data Quality Issues 2.3 Data Types and Type Detection

4 Chapter 3: Transform and Load the Data

3.1 Column Transformations and Data Types 3.2 Grouping, Aggregating, Pivoting, and Unpivoting 3.3 Merge Queries, Append Queries, and Relationship Keys 3.4 Query Folding and Load Performance

5 Chapter 4: Design and Implement a Data Model

4.1 Star Schema Design 4.2 Relationships, Cardinality, and Cross-Filter Direction 4.3 Table and Column Properties 4.4 Calculated Columns and Calculated Tables

6 Chapter 5: Create Model Calculations with DAX

5.1 DAX Fundamentals and Measures 5.2 The CALCULATE Function 5.3 Time Intelligence Measures 5.4 Statistical Functions, Semi-Additive Measures, and Calculation Groups 5.5 Essential DAX Patterns and Functions Reference

7 Chapter 6: Optimize Model Performance

6.1 Performance Analyzer and DAX Query View 6.2 Model Size Reduction and Cardinality Management 6.3 DAX Optimization Patterns

8 Chapter 7: Create Reports

7.1 Visual Types and Selection 7.2 Visual Formatting and Conditional Formatting 7.3 Copilot in Power BI Reports 7.4 Report Page Configuration and Slicers

9 Chapter 8: Enhance Reports for Usability and Storytelling

8.1 Bookmarks, Navigation, and Drillthrough 8.2 Sorting, Selection Pane, Mobile Layout, and Accessibility 8.3 Export Settings and Automatic Page Refresh

10 Chapter 9: Identify Patterns and Trends

9.1 The Analyze Feature and AI Visuals 9.2 Using Copilot to Summarize the Semantic Model 9.3 Advanced Analytics Patterns

11 Chapter 10: Create and Manage Workspaces and Assets

10.1 Workspaces, Apps, and Content Distribution 10.2 Subscriptions, Alerts, and Semantic Model Refresh 10.3 Dashboards and Advanced Distribution

12 Chapter 11: Secure and Govern Power BI Items

11.1 Row-Level Security (RLS) 11.2 Sensitivity Labels, Item Access, and Governance 11.3 Deployment Pipelines and Content Lifecycle

3.3 Merge Queries, Append Queries, and Relationship Keys

Key Takeaways Explain This Topic AI

Merge Queries joins two tables horizontally based on matching columns (like SQL JOIN).

Append Queries stacks two tables vertically with matching column structures (like SQL UNION).

Six join types are available: Left Outer, Right Outer, Full Outer, Inner, Left Anti, Right Anti.

Reference queries create a pointer to an existing query without duplicating data; Duplicate queries create an independent copy.

Relationship keys must be unique in at least one table (the 'one' side) and share the same data type.

Last updated: June 2026

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

Join Type Description Returns

Left Outer All rows from left table + matching rows from right Left table complete, nulls for non-matches from right

Right Outer Matching rows from left + all rows from right table Right table complete, nulls for non-matches from left

  • Full Outer All rows from both tables Both tables complete, nulls where no match
  • Inner Only matching rows from both tables Only rows with matches in both
  • Left Anti Rows from left table with NO match in right Left-only rows (useful for finding missing data)
  • Right Anti Rows from right table with NO match in left Right-only rows
  • Merge Process
  • Select the first (left) table
  • Select the matching column(s) in the first table
  • Select the second (right) table
  • Select the matching column(s) in the second table
  • Choose the join type
  • Click OK → a new column with nested tables appears
  • Expand the nested column to select which fields to include
  • Merge Queries vs. Merge Queries as New
  • Option Behavior
  • Merge Queries Adds the merged data to the current query
  • Merge Queries as New Creates a new query with the merged result

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

  • Option Use Case
  • Two Tables Combine exactly two queries
  • Three or More Tables Combine multiple queries at once
  • 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
  • Feature Merge Append
  • Direction Horizontal (adds columns) Vertical (adds rows)
  • SQL Equivalent JOIN UNION ALL
  • Key Required Yes (matching columns) No (matched by name)
  • Use Case Combining related data Combining same-structure data
  • 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
  • Scenario Use
  • Creating a filtered subset of a base query Reference
  • Creating a staging query that feeds multiple outputs Reference
  • Isolating error rows for investigation Reference
  • Creating an independent query starting from similar logic Duplicate
  • 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:

  • Action How
  • Load to model Right-click query → Enable Load (checked)
  • Staging only (not loaded) Right-click query → Enable Load (unchecked)
  • Load to both model and Excel Power Query → Close & Load To → select destinations

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