Launch Deal · 50% off 1st month ·

1.8 — Merge and Append Queries, and Configure Data Loading

PL-300 Domain 1 · Skill 1.8

What You Need to Know

Merge vs Append

Merge Queries Append Queries
Combines Columns from another table, matched on key columns Rows from other tables, stacked
Like A SQL JOIN or Excel XLOOKUP A SQL UNION ALL
Typical use Add a product's category to sales lines; find missing matches Combine Sales2025 and Sales2026, or one table per region
Result A new column of nested tables to expand Columns matched by name. A column missing in one table is filled with null there

Both have an …as New option that creates a new query instead of changing the current one.

Join kinds

Join kind Keeps
Left outer (default) All rows from the first table, matching rows from the second
Right outer All rows from the second table, matching rows from the first
Full outer All rows from both
Inner Only rows that match in both
Left anti Rows in the first table with no match in the second (orders without a known customer)
Right anti Rows in the second table with no match in the first
  • Merge on several columns by Ctrl-clicking them in the same order in both tables.
  • Fuzzy matching joins values that are similar but not equal ("Contoso Ltd" and "Contoso Limited"). You set a similarity threshold from 0 to 1, choose whether to ignore case, and can supply a transformation table that maps known variants.
  • Use anti joins to audit data quality before relating tables.
  • After merging, expand only the columns you need. If the second table has duplicate keys, the merge multiplies rows.

Configure data loading

Right-click a query in the Queries pane:

Option Effect
Enable load When cleared, the query still runs as a source for other queries but is not loaded into the model. Its name shows in italics
Include in report refresh When cleared, Refresh skips this table, which keeps its last loaded data. Useful for static tables that are slow to load

Turn off load for staging queries, helper queries and parameters' lookup queries, so data isn't stored twice and the field list stays clean.

Under File > Options > Current file > Data Load you can also control Auto date/time (lesson 2.3), relationship detection on load, and parallel loading of tables.

Key Concept: Merge widens a table (adds columns), Append lengthens it (adds rows). Load only the queries the report uses, and keep staging queries as unloaded building blocks.

Exam Traps

  • Appending tables whose column names differ in spelling or case, which creates extra columns full of nulls
  • Using an inner join when the requirement says to keep every sales row even without a matching product
  • Using Merge when the requirement is to combine two years of the same table (that's Append)
  • Clearing Include in report refresh on a table that must stay current
  • Deleting a staging query instead of disabling its load, which breaks every query that references it

Check your understanding

Practice Scenario

You need to add each product's Category to the Sales query. Every sales row must be kept, including rows whose ProductKey has no match in the Product query. What should you do?

Report issue

Log in to save your progress.

Sources

You've finished the free part of PL-300

The lessons stay free. Pro unlocks the other 22 PL-300 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.

See plans

Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.

Cancel anytime. Or €199 a year, which saves 17%.

Not sure yet? Try 10 free mock exam questions.