Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is importing data from an Excel workbook into Power BI. The workbook contains three sheets: 'Sales2022', 'Sales2023', and 'ProductCatalog'. The analyst needs to combine the sales data from 'Sales2022' and 'Sales2023' into a single table and also load 'ProductCatalog' as a separate table. The 'Sales2022' and 'Sales2023' sheets have identical structures. Which Power Query operation is the most efficient way to achieve this for the sales data?
- APivot Column
- BAppend Queries
- CMerge Queries
- DGroup By
Show answer & explanationAnswer & explanation
Correct answer: B. Append Queries
Append Queries is used to stack rows from multiple tables into a single table, assuming they have compatible column structures. This is precisely what's needed to combine 'Sales2022' and 'Sales2023' into one sales table.
Why the other options are wrong
- A. Pivot Column transforms rows into columns, which is a data reshaping operation, not a combining operation for tables with identical structures.
- C. Merge Queries combines tables horizontally based on matching columns, not vertically by stacking rows.
- D. Group By aggregates data based on one or more columns, which is not the goal here.
Append Queries
Append Queries in Power Query combines two or more tables by stacking their rows on top of each other. This operation requires the tables to have compatible column structures (same column names and data types).
- Combines rows from multiple tables vertically.
- Requires compatible column structures.
- Useful for consolidating data from similar sources (e.g., yearly sales files).
Memory trick: Append stacks, Merge joins, Group aggregates.