Microsoft Power BI Quiz: Merging Queries

10 questions · exam conditions

0:00

Merging QueriesQuestion 1 of 10

A Sales query contains every transaction and a CustomerID column. A Customers query contains one row per known customer and includes Region. Some sales transactions reference customer IDs that are not yet in Customers. The report must preserve every sales transaction, enrich known customers with Region, and show null for Region when a customer is unknown.

Which merge configuration should you use?

Place Sales first and Customers second, then use a left outer join

Place Sales first and Customers second, then use an inner join

Place Customers first and Sales second, then use a left outer join

Place Sales first and Customers second, then use a full outer join

← Back to quizzes

Microsoft Power BI Quiz

Microsoft Power BI Quiz: Merging Queries

Practice Merging Queries in Microsoft Power BI with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Merging Queries, giving you a quick way to practice the rules, question types, and explanations that matter most for Microsoft Power BI.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

Which merge configuration should you use?

Place Sales first and Customers second, then use a left outer join (correct answer)
Place Sales first and Customers second, then use an inner join
Place Customers first and Sales second, then use a left outer join
Place Sales first and Customers second, then use a full outer join

Explanation: 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.

Question 2

SystemA and SystemB each contain a distinct AccountID column. You must create a reconciliation query that includes accounts found in either system. The result must also allow you to classify each account as present in both systems, present only in SystemA, or present only in SystemB.

Which merge approach best supports this requirement?

Use an inner join and classify null values after expanding both account columns
Use a left outer join from SystemA and classify the expanded SystemB column
Use a full outer join and retain identifiers from both sides for classification (correct answer)
Use a left anti join from SystemA and append the unmatched SystemB accounts

Explanation: 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.

Question 3

Products contains one row per ProductID. WarrantyClaims contains multiple claims per product, each with a ClaimCost. The final query must contain every product exactly once and a TotalClaimCost value. Products without claims must remain in the result.

Which Power Query workflow avoids unnecessary row multiplication?

Left-outer merge Claims into Products and aggregate ClaimCost from the nested column (correct answer)
Inner-merge Claims into Products, expand ClaimCost, and remove duplicate ProductID rows
Full-outer merge Claims into Products and replace duplicate ProductID values with null
Left-anti merge Claims into Products and calculate ClaimCost after loading the query

Explanation: 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.

Question 4

A Customers query stores CustomerKey as five-character text, including leading zeros, such as "00125". An Orders query imports the corresponding key as a whole number, such as 125. A merge currently fails to match these records. The five-character customer key is the organization's canonical format and may be displayed in reports.

What should you do before merging the queries?

Convert Customers[CustomerKey] to a whole number and merge it with the numeric order key
Convert Orders[CustomerKey] to text, pad it to five characters, and then merge (correct answer)
Leave the data types unchanged and enable fuzzy matching on both key columns
Merge the columns as imported and replace null customer values after expansion

Explanation: 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.

Question 5

Inventory contains one row for each StoreID and ProductID combination. ProductTargets also contains targets by StoreID and ProductID. ProductID values are reused across stores. A merge using only ProductID produces duplicate and incorrect target matches.

How should you configure the merge keys to return the target for the same product in the same store?

Select ProductID in both queries and remove duplicate target rows after expansion
Select StoreID in both queries and use ProductID only as an expanded column
Select StoreID and then ProductID in both queries in corresponding order (correct answer)
Select ProductID and then StoreID in Inventory but reverse the order in ProductTargets

Explanation: 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.

Question 6

A PurchaseOrders query contains SupplierName values such as "Northwind Trading". A Suppliers query contains the canonical supplier names, but the purchase-order names can contain minor spelling differences and inconsistent capitalization. SupplierCode is exact and must not be matched approximately. Both SupplierName columns are typed as Text.

Which approach is most appropriate for merging the supplier records?

Full-outer merge on SupplierName and assume unmatched rows represent spelling differences
Fuzzy-merge on SupplierCode with a low threshold, then expand the canonical SupplierName
Exact-merge on SupplierName after changing both columns from Text to Any
Fuzzy-merge on SupplierName with a reviewed similarity threshold, then validate ambiguous matches (correct answer)

Explanation: 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.

Question 7

An Orders query contains 100 rows and has a unique OrderID column. A Returns query contains no matching row for 92 orders, one matching row for each of six orders, and three matching rows for each of two orders. You merge Returns into Orders by using OrderID and a left outer join, and then expand ReturnReason.

How many rows will the expanded query contain?

100 rows, because a left outer join always preserves the original row count
104 rows, because multiple return matches expand the corresponding order rows (correct answer)
106 rows, because every matching return is added to the original order rows
12 rows, because only orders having at least one return remain after expansion

Explanation: 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 \times 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."

Question 8

A Sales query should reference only valid products from ProductMaster. You must create a separate exception query containing sales rows whose ProductID does not occur in ProductMaster. ProductMaster can contain additional products that have never been sold.

Which join should you use to create the exception query directly?

Merge Sales first with ProductMaster by using a left anti join (correct answer)
Merge Sales first with ProductMaster by using a left semi join
Merge ProductMaster first with Sales by using a left anti join
Merge Sales first with ProductMaster by using a full outer join

Explanation: 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.

Question 9

Customers has one row per CustomerID. Orders can have many rows for the same CustomerID. You need a query containing one row for each customer who has at least one order. You do not need any columns from Orders, and duplicate customer rows must not be generated.

Which merge configuration most directly meets the requirement?

Merge Customers first with Orders by using an inner join, then expand OrderID
Merge Customers first with Orders by using a left semi join (correct answer)
Merge Customers first with Orders by using a left outer join
Merge Orders first with Customers by using a right semi join

Explanation: 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.

Question 10

In the Merge dialog, Shipments is the first query and Warehouses is the second query. A warehouse can have multiple shipments, and some warehouses have none. You must produce one row for each shipment plus one row for every warehouse that has no shipments. No shipment may be removed.

Which join type should you select without reversing the query order?

Left outer, retaining all rows from Shipments and matching rows from Warehouses
Full outer, retaining unmatched rows from both queries as separate exceptions
Inner, retaining only warehouses and shipments whose keys occur in both queries
Right outer, retaining all rows from Warehouses and matching rows from Shipments (correct answer)

Explanation: 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.