2.3.5. Reference vs. Duplicate, Merge vs. Append
💡 First Principle: Two pairs of look-alike operations drive many PL-300 distractors. Reference vs. duplicate is about dependency: does the new query follow the original, or stand alone? Merge vs. append is about direction: are you adding columns from a matching table, or rows from a similar table?
Reference creates a new query whose source is the output of the original query. Change the original (add a cleaning step) and every referencing query inherits it. Use it to build several dimensions from one staging query, or to branch a cleaned table into different shapes, so logic is written once. Duplicate copies every applied step into a new, independent query; later changes to the original don't flow to the copy. Use it when you want to experiment safely or create a variant that must diverge.
Merge Queries joins two tables on matching key columns, adding the second table's columns as a nested table column you then expand. It's the Power Query equivalent of a SQL JOIN. Merge Queries as New produces a third query instead of modifying the first. The join kind determines which rows survive:
| Join kind | Keeps | Typical use |
|---|---|---|
| Left outer (default) | All rows from first table, matches from second | Look up attributes for every fact row |
| Right outer | All rows from second table, matches from first | Rarely needed; reverse of left |
| Full outer | All rows from both | Reconcile two lists |
| Inner | Only matching rows | Keep only records present in both |
| Left anti | Rows in first with no match in second | Find orphans: orders with unknown customers |
| Right anti | Rows in second with no match in first | Find dimension members never used |
Fuzzy matching (an option in the Merge dialog) matches approximately similar text values using a similarity threshold (0 to 1), plus options to ignore case and match by combining text parts, which helps when names are spelled inconsistently across sources.
Append Queries stacks tables vertically, like a SQL UNION ALL, choosing two tables or three or more tables. Columns are matched by name; a column that exists in only one table appears in the result with nulls for the other table's rows. Use it to combine monthly extracts, regional files, or current and archive tables with the same structure. (The Folder connector automates appending many files of the same shape.)
⚠️ Exam Trap: "Combine the 2025 and 2026 order tables into one table" is Append; "add the customer's segment to each order" is Merge. And when the requirement is "identify orders whose customer doesn't exist in the Customer table," the precise answer is a left anti join, not an inner join followed by filtering.
Reflection Question: You build Product, Customer, and Sales queries from one staging query and later discover the staging query needs an extra Trim step. Explain why your choice between Reference and Duplicate at the start determines whether you edit one query or four.
