Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Medium
A data engineer is working on a Dataflow Gen2 to ingest product catalog data from an on-premises SQL Server database. The database contains multiple tables (e.g., Products, Categories, Suppliers) that need to be combined into a single, denormalized table in the Lakehouse. The engineer wants to perform a series of JOIN operations between these tables within the Dataflow Gen2's Power Query Editor to achieve the denormalized structure. Which Power Query transformation function should the engineer use to combine two tables based on a common key?
- AAppend Queries
- BMerge Queries
- CPivot Column
- DGroup By
Show answer & explanationAnswer & explanation
Correct answer: B. Merge Queries
In Power Query, 'Merge Queries' is the function used to combine two tables (queries) based on matching values in one or more common columns, similar to a SQL JOIN operation. This is precisely what is needed to denormalize data by bringing together related information from separate tables.
Why the other options are wrong
- A. 'Append Queries' is used to stack rows from one table onto another (union operation), not to join columns based on a key.
- C. 'Pivot Column' transforms rows into columns, which is a different type of data reshaping, not for joining tables.
- D. 'Group By' aggregates rows based on common values in specified columns, not for combining tables horizontally.
Power Query Merge Queries
A Power Query transformation that combines two queries (tables) into a single query based on matching values in specified columns, similar to a SQL JOIN.
- Used for horizontal combination of data.
- Supports various join kinds (inner, left outer, right outer, full outer).
- Essential for denormalization and bringing related data together.
Memory trick: Merge is like two rivers joining, combining their waters into one flow.