Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is building a Power BI report for a sales team. The sales data is stored in an Excel workbook, and the analyst needs to combine detailed sales transactions (Sheet1) with customer demographic information (Sheet2). Both sheets share a common 'CustomerID' column. The requirement is to include all sales transactions, and only the customer information that has a matching 'CustomerID' in the sales data. Which type of join in Power Query should the analyst use?
- ARight Outer (All from second, matching from first)
- BLeft Outer (All from first, matching from second)
- CFull Outer (All Rows)
- DInner (Only Matching Rows)
Show answer & explanationAnswer & explanation
Correct answer: B. Left Outer (All from first, matching from second)
A Left Outer join includes all rows from the first (left) table (sales transactions) and only the matching rows from the second (right) table (customer demographics). This perfectly matches the requirement to keep all sales and only corresponding customer data.
Why the other options are wrong
- A. A Right Outer join would include all customer information and only matching sales transactions, which is the opposite of the requirement.
- C. A Full Outer join would include all sales and all customer information, even if there's no match in the other table, which goes beyond the specified requirement.
- D. An Inner join would only include sales transactions that have a matching customer, potentially excluding some sales if a customer ID was missing from the customer sheet.
Left Outer Join (Power Query)
A type of merge operation in Power Query that keeps all rows from the first (left) table and includes matching rows from the second (right) table. If no match is found in the right table, nulls are returned for the columns from the right table.
- Preserves all rows from the 'left' query.
- Returns matching rows from the 'right' query.
- Returns nulls for 'right' columns if no match is found.
Memory trick: When merging, think about who's 'left' standing and who gets 'right' in.