- Which merge configuration most directly meets the requirement?
Answer: Merge Customers first with Orders by using a left semi join
When filtering one table based on the existence of matching rows in another — without needing columns from the second table — the key concept is semi joins. A semi join returns rows from the left table only when a match exists in the right table, and critically, it never duplicates rows or adds columns from the right side. This is exactly what the question asks for.
A left semi join on Customers-to-Orders returns each Customer row that has at least one matching Order, with no extra columns pulled in and no row multiplication — making B the most direct solution. You get one row per qualifying customer, nothing more.
A fails because expanding OrderID after an inner join defeats the "no duplicates" requirement. A customer with five orders produces five rows in the result, forcing you to add a deduplication step afterward — extra, unnecessary work.
C uses a left outer join, which keeps all customers (including those with no orders) and again multiplies rows for customers with multiple orders. You'd need to filter and deduplicate, making it a roundabout solution.
D is a right semi join on Orders-to-Customers. Power Query's right semi join returns rows from the right table (Customers) that have a match in the left table (Orders) — which sounds equivalent, but the question specifies merging Customers first, so Orders is the left table here. This works logically but is less direct and swaps the primary table unnecessarily.
Study tip: On Power BI exam questions about filtering existence without column expansion, immediately think "semi join." Left semi = "keep left rows that have a match" — no duplicates, no extra columns, no cleanup needed.
- Which Power Query workflow avoids unnecessary row multiplication?
Answer: Left-outer merge Claims into Products and aggregate ClaimCost from the nested column
When combining a one-to-many relationship in Power Query, your goal is to produce exactly one output row per "one" side (Products) while still aggregating data from the "many" side (WarrantyClaims). The trap most students fall into is expanding nested tables too early, which multiplies rows before aggregation happens.
Option A is the correct workflow because a left-outer merge keeps every Product row exactly once, and the WarrantyClaims data arrives as a nested table in a single column. You can then use Table.TransformColumns or the "Aggregate" option directly on that nested column to sum ClaimCost — no row multiplication ever occurs. Products with no claims simply get a nested empty table, which aggregates to zero or null cleanly.
Option B is tempting but fundamentally flawed: an inner merge already drops products with no claims, and expanding ClaimCost before deduplication creates one row per claim. Removing duplicates afterward is fragile — you lose the ability to sum costs correctly since you're just discarding rows rather than aggregating them.
Option C uses a full-outer merge, which not only keeps all Products but also introduces standalone Claim rows with no matching ProductID, polluting your result. Replacing duplicate ProductID values with null doesn't aggregate anything — it's a data-masking workaround, not a proper aggregation strategy.
Option D, a left-anti merge, returns only Products that have no matching claims, which is the opposite of what you need. You'd lose all products that actually have warranty data.
Study tip: Whenever a question asks you to aggregate a "many" side onto a "one" side, think "merge then aggregate the nested column" — never expand before aggregating.
- Which merge approach best supports this requirement?
Answer: Use a full outer join and retain identifiers from both sides for classification
When building a reconciliation query in Power BI, the key question to ask yourself is: do I need to see every record from both sources, regardless of whether a match exists? If yes, a full outer join is your tool, because it returns all rows from both tables and places nulls where no match was found on either side.
A full outer join is precisely what option C describes. By retaining the AccountID from both SystemA and SystemB in the result, you can inspect each row and classify it: if both identifiers are populated, the account exists in both systems; if only the SystemA column has a value, it's exclusive to SystemA; and if only the SystemB column has a value, it's exclusive to SystemB. This single query satisfies all three classification requirements cleanly.
Option A is flawed because an inner join only returns rows where a match exists in both tables — accounts unique to either system are dropped entirely before you even get to classify nulls, making reconciliation impossible.
Option B, a left outer join from SystemA, captures all SystemA accounts and their SystemB matches, but silently discards accounts that exist only in SystemB. You'd produce an incomplete reconciliation without realizing it.
Option D gets closer in spirit but is ultimately a workaround. A left anti join returns only SystemA accounts with no match, and appending unmatched SystemB records creates two separate datasets that you then stitch together manually — a fragile, multi-step process compared to the elegance of a single full outer join.
Remember this rule of thumb: whenever a question mentions "accounts found in either system" with full classification, that language is a direct signal to reach for the full outer join.
- Which join should you use to create the exception query directly?
Answer: Merge Sales first with ProductMaster by using a left anti join
When working with merge joins in Power Query, the key question to ask is: "Which table drives the result, and which rows do I want to keep or exclude?" Anti joins are specifically designed to return rows from one table that have no match in the other — making them perfect for exception or orphan-record queries.
In this scenario, you want Sales rows where ProductID is absent from ProductMaster. A left anti join returns all rows from the left table that have no corresponding match in the right table. So when you merge Sales (left) with ProductMaster (right) using a left anti join, you get exactly the orphaned sales records — the exception query you need. That's why A is correct.
B is wrong because a left semi join is the opposite of what you want — it returns Sales rows that do have a matching ProductID in ProductMaster. This would give you valid sales, not exceptions.
C flips the table order, placing ProductMaster on the left. A left anti join would then return products in ProductMaster with no sales — which is explicitly described as acceptable in the passage, not an exception scenario. This is a common trap: the join type is right, but the table order reverses the logic entirely.
D is wrong because a full outer join returns all rows from both tables, including matches. You'd need additional filtering steps to isolate exceptions, making it an indirect and inefficient solution.
Your study tip: always identify which table's unmatched rows you need. "Left" always refers to the first table listed in the merge — so table order matters as much as join type.
- How many rows will the expanded query contain?
Answer: 104 rows, because multiple return matches expand the corresponding order rows
When merging queries in Power BI using a left outer join, the key concept to master is how row multiplication works during expansion. A left outer join preserves every row from the left table (Orders), but when a matching row in the right table (Returns) has multiple records for a single key, that left-side row is duplicated once per match.
Walk through the math here: you have 100 orders total. 92 orders have no match in Returns — they stay as single rows, contributing 92 rows. Six orders each have exactly one matching return — they remain one row each, contributing 6 rows. Two orders each have three matching returns — each of those two rows expands into three rows, contributing 2×3=6 rows. The total is 92+6+6=104 rows, confirming B is correct.
Choice A is the most tempting trap — it assumes a left outer join freezes the row count at 100. That's only true if no order has more than one match. The moment a right-side key appears multiple times, the corresponding left-side row multiplies. Choice C (106 rows) likely comes from mistakenly counting each of the two multi-return orders as contributing two extra rows rather than thinking through the expansion correctly. Choice D (12 rows) describes behavior closer to an inner join that filters to only matched rows — a left outer join never discards unmatched left-side rows.
As a study tip: whenever you see a merge/expand scenario on the Power BI exam, explicitly count your one-to-many matches and calculate row multiplication before choosing an answer. Don't assume row count stays fixed just because the join type is "left outer." - What should you do before merging the queries?
Answer: Convert Orders[CustomerKey] to text, pad it to five characters, and then merge
When merging queries in Power BI, both key columns must share the same data type and the same value format — not just the same underlying number. This question tests whether you understand that a successful merge requires exact value matching, not just logical equivalence.
The canonical format here is a five-character, zero-padded text key ("00125"). Since this format must also appear in reports, you should preserve it as the standard. The correct approach, B, converts Orders[CustomerKey] from a whole number to text and then pads it with leading zeros to match the five-character format. Power Query's Text.PadStart function (or the UI equivalent) achieves this. Once both columns are text values like "00125", the merge engine can match them exactly.
A goes the wrong direction — converting the Customers key to a whole number strips the leading zeros, destroying the canonical format the organization requires for display. You'd solve the merge but break reporting.
C sounds appealing because fuzzy matching seems like it could bridge format differences, but fuzzy matching is designed for approximate string similarity (e.g., "Microsoft" vs. "Microsft"), not for reconciling a text "00125" against an integer 125. It will not reliably resolve numeric-vs-text type mismatches.
D suggests merging incompatible columns and patching nulls afterward, but the merge simply won't produce matches when data types differ — you'd get an entirely null expanded column, not a fixable partial result.
Your study tip: whenever a merge fails, check both the data type and the formatted value. Fixing only the type (e.g., converting to text) isn't enough if the values still differ in format.
- Which approach is most appropriate for merging the supplier records?
Answer: Fuzzy-merge on SupplierName with a reviewed similarity threshold, then validate ambiguous matches
When you encounter a merge question in Power BI, ask yourself two things: how reliable is each key column? and what kind of variation exists in the data? Here, SupplierName has known inconsistencies (spelling, capitalization), while SupplierCode is exact — that distinction drives everything.
Fuzzy merge in Power Query is designed precisely for text columns where human entry introduces minor variations. By merging on SupplierName with a carefully chosen similarity threshold, you let Power Query surface probable matches without forcing an exact-string requirement. The critical word in option D is "reviewed" — fuzzy matching can produce false positives, so validating ambiguous matches before finalizing the merge is best practice and what makes D the right answer.
Option A is tempting but flawed: a full outer merge on SupplierName would silently treat spelling differences as distinct, unmatched rows rather than resolving them. You'd end up with nulls where you actually have a valid match, and no mechanism to reconcile the differences.
Option B misuses the fuzzy-merge feature by applying it to SupplierCode — a column the passage explicitly says must be matched exactly. Using a low threshold on an exact key risks joining the wrong suppliers together, which is worse than no match at all.
Option C changes both columns to the Any data type, but data type alone has no effect on match logic. An exact merge on SupplierName still fails whenever there's a single character difference or case mismatch.
Study tip: On Power BI exam questions, watch for columns described as "exact" vs. "approximate." Fuzzy merge is only appropriate for the approximate column — never apply it where exactness is explicitly required.
- Which merge configuration should you use?
Answer: Place Sales first and Customers second, then use a left outer join
When merging queries in Power BI, the key question is always: which table drives the result set? The table you place first determines which rows are preserved. A left outer join keeps all rows from the first (left) table and brings in matching columns from the second (right) table, filling unmatched rows with null.
In this scenario, Sales is your source of truth — every transaction must appear in the final result. Customers is your enrichment table. Placing Sales first and using a left outer join means every sales row survives, matched customers contribute their Region, and unmatched customers (those not in the Customers table) simply receive a null for Region. That's exactly what the requirements describe, making A the correct configuration.
B is wrong because an inner join only returns rows where a match exists in both tables. Any sales transaction referencing an unknown customer ID would be silently dropped — violating the requirement to preserve every transaction.
C fails because swapping the table order makes Customers the left (driving) table. A left outer join would then preserve every customer row, but sales transactions from unknown customers — the very rows you need to keep — would be discarded or missing entirely.
D is wrong because a full outer join returns all rows from both tables, including customers with no sales at all. This inflates your result set with irrelevant customer-only rows and doesn't reflect a clean "enrich sales with customer data" pattern.
Study tip: On Power BI questions about merges, always identify which table must lose zero rows — that table goes first, and a left outer join does the rest.
- Which join type should you select without reversing the query order?
Answer: Right outer, retaining all rows from Warehouses and matching rows from Shipments
When working with Power Query merge joins, your goal is to identify which table is the "driving" table — the one whose rows must all be preserved. The query order in the Merge dialog matters: the first query is the left side, and the second query is the right side.
Here, the requirements are: keep every shipment (no shipment may be removed) AND keep every warehouse that has no shipments. Since every shipment already has a warehouse, the real challenge is pulling in warehouses with zero shipments. That means you need all rows from Warehouses (the right/second query) plus all matching Shipments rows. This is exactly what a right outer join does — it retains every row from the right query (Warehouses) and matches what it can from the left query (Shipments). Since every shipment has a warehouse, no shipment gets dropped either. D is correct.
A is wrong because a left outer join retains all rows from Shipments (the first/left query) and only matching warehouses — warehouses with no shipments would be excluded entirely, violating the requirement.
B is wrong because a full outer join would also include unmatched shipments as separate rows. Since all shipments are matched to a warehouse, this isn't harmful here, but the scenario says warehouses with no shipments need representation — full outer is overkill and not the precise, intended tool for this scenario.
C is wrong because an inner join only returns rows where keys exist in both tables, which immediately drops warehouses that have no shipments.
A useful memory trick: think of "right outer" as "right wins." If the right-side table is the one that must contribute all its rows, choose right outer — no need to reverse the query order.
- How should you configure the merge keys to return the target for the same product in the same store?
Answer: Select StoreID and then ProductID in both queries in corresponding order
When merging two tables in Power Query on multiple columns, the order and pairing of keys matters — Power Query uses a composite key, matching the first selected column in one table to the first in the other, the second to the second, and so on. This question tests whether you understand how to construct that composite key correctly when a single column isn't unique enough to identify a row.
Because ProductID values repeat across stores, merging on ProductID alone causes multiple rows from ProductTargets to match a single Inventory row — giving you incorrect, duplicated targets. The correct fix is to merge on both StoreID and ProductID together, creating a composite key that uniquely identifies each combination. In Power Query's Merge dialog, you hold Ctrl and click StoreID first, then ProductID in both tables, in the same corresponding order. This is exactly what option C describes, and it's the only approach that guarantees each Inventory row is matched to its single correct target row.
Option A is flawed because removing duplicates after expansion is a workaround that doesn't fix the root cause — you may still silently retain wrong matches before deduplication. Option B only joins on StoreID, which is too broad; many products share the same store, so you'd still get multiple target matches per row. Option D describes reversing the key order between tables, which would cause Power Query to cross-match columns incorrectly — StoreID in one table paired with ProductID in the other — producing meaningless results.
The study tip here: in Power Query merges, always ask "does this key combination uniquely identify each row?" If not, add more columns — and keep their selection order consistent across both tables.
