Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
You are building a Power BI report for a sales team. You need to combine data from an Excel workbook containing monthly sales figures and a SQL Server database table that stores customer demographics. Both sources have a common column, `CustomerID`, which uniquely identifies each customer. You want to ensure that all sales records are included, even if there is no corresponding customer demographic information. Which Power Query join kind should you use?
- ARight Outer (all from second, matching from first)
- BFull Outer (all rows from both)
- CLeft Outer (all from first, matching from second)
- DInner (only matching rows)
Show answer & explanationAnswer & explanation
Correct answer: C. Left Outer (all from first, matching from second)
To include all records from the primary sales table (the 'first' table) and only the matching records from the customer demographics table (the 'second' table), a Left Outer join is the appropriate choice. This ensures no sales data is lost.
Why the other options are wrong
- A. Right Outer join would include all customer demographic records and only matching sales records, potentially excluding sales data without customer info.
- B. Full Outer join would include all rows from both tables, potentially creating nulls for sales records without customer data and vice-versa, which isn't the primary goal here.
- D. Inner join would only include sales records that have a matching customer demographic entry, excluding sales without customer data.
Left Outer Join
A join in Power Query that includes all rows from the first (left) table and only the matching rows from the second (right) table. Non-matching rows from the second table will have nulls.
- Preserves all records from the 'left' table.
- Only includes matching records from the 'right' table.
- Results in nulls for non-matching columns from the 'right' table.
Memory trick: Left for all, Right for some, Inner for common, Full for all.