PL-300 · Question #297
Drag and Drop Question In Power Query Editor, you have three queries named ProductCategory, ProductSubCategory, and Product. Every Product has a ProductSubCategory. Not every ProductsubCategory has…
The correct answer is Inner; Left outer. This question tests knowledge of Power Query merge join types and how to select the most performant join based on known referential integrity between tables. The goal is to choose joins that avoid unnecessary null handling while preserving all required rows.
Question
Answer Area
Drag items
Correct arrangement
- Inner
- Left outer
Explanation
This question tests knowledge of Power Query merge join types and how to select the most performant join based on known referential integrity between tables. The goal is to choose joins that avoid unnecessary null handling while preserving all required rows.
Approach. For merging Product into ProductSubCategory, use Inner join - because every Product is guaranteed to have a ProductSubCategory, an inner join returns the same result as a left outer join but performs better since Power Query can skip null-handling logic for unmatched rows. For merging ProductSubCategory into ProductCategory, use Left outer join - because NOT every ProductSubCategory has a parent ProductCategory, so a left outer join is required to retain all subcategory rows even when no matching category exists, preventing data loss. The performance principle here is: use Inner when referential integrity is guaranteed (no rows will be dropped), and fall back to Left outer only when the relationship is optional and you must preserve unmatched rows from the left table.
Concept tested. Power Query merge join type selection for performance optimization based on table cardinality and referential integrity (Inner vs. Left outer joins in star/snowflake schema modeling)
Reference. Microsoft Learn - Merge queries overview (Power Query) / PL-300 exam domain: Prepare the data
Community Discussion
No community discussion yet for this question.