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?
Log in to save your progress.
Sources
- Merge queries overview, Microsoft Learn
- Append queries, Microsoft Learn
- Fuzzy merge, Microsoft Learn
- Data reduction techniques for Import modeling, Microsoft Learn
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.
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.