Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is combining two tables in Power Query: 'Sales' (containing daily sales transactions) and 'Products' (containing product details like category and price). Both tables have a 'ProductID' column. The requirement is to include all sales transactions, even those with a 'ProductID' that does not exist in the 'Products' table, and to show product details where they are available. Which type of join should be used?
- AInner Join
- BRight Outer Join
- CLeft Outer Join
- DLeft Anti Join
Show answer & explanationAnswer & explanation
Correct answer: C. Left Outer Join
A Left Outer Join includes all rows from the first (left) table and the matching rows from the second (right) table. If there's no match, the columns from the right table will have nulls. This perfectly matches the requirement to include all sales transactions (from the left 'Sales' table) and product details where available.
Why the other options are wrong
- A. Inner Join would only include sales transactions where a matching ProductID exists in both tables, excluding transactions without product details.
- B. Right Outer Join would include all products and only matching sales transactions, which is not the requirement (all sales transactions are needed).
- D. Left Anti Join would only return sales transactions that do NOT have a matching ProductID in the Products table, which is the opposite of the requirement.
Left Outer Join
A Left Outer Join in Power Query combines two tables by including all rows from the first (left) table and only the matching rows from the second (right) table. If no match is found for a left row, the columns from the right table will contain null values.
- Returns all rows from the left table.
- Returns matching rows from the right table.
- Pads non-matching right rows with nulls.
Memory trick: Left keeps all left, Right keeps all right, Inner only common, Outer keeps all.