Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Medium
A global e-commerce company needs to ingest product catalog data from multiple regional SQL Server databases into a centralized Fabric Lakehouse. Each regional database has an identical schema for the product table. The solution must ensure that data from all regions is combined into a single table in the Lakehouse, with minimal manual effort for schema evolution. Which Dataflows Gen2 feature is best suited for this scenario?
- AUtilizing the 'Append Queries' feature in Power Query Editor.
- BUsing separate Dataflows for each region and manually appending results.
- CCreating a single Dataflow Gen2 with a 'Combine Files' transformation.
- DWriting a custom M query to dynamically merge data sources.
Show answer & explanationAnswer & explanation
Correct answer: A. Utilizing the 'Append Queries' feature in Power Query Editor.
The 'Append Queries' feature in Power Query Editor (within Dataflows Gen2) is specifically designed to combine data from multiple tables (or queries) with identical schemas into a single, unified table. This automates the union operation and minimizes manual effort for managing common schemas.
Why the other options are wrong
- B. While possible, manually appending results would be cumbersome and prone to errors, especially with many regions or schema changes.
- C. The 'Combine Files' feature is for combining multiple files (e.g., CSV, Excel) from a folder, not for combining multiple database tables directly.
- D. Writing a custom M query can achieve this, but 'Append Queries' provides a visual, low-code way to achieve the same, which is generally preferred for maintainability and ease of use in Dataflows Gen2.
Power Query Append Queries
The 'Append Queries' feature in Power Query Editor (part of Dataflows Gen2) allows combining rows from two or more queries (tables) into a new single query, assuming they have compatible schemas. It's ideal for uniting similarly structured data from multiple sources.
- Combines rows from multiple queries/tables.
- Requires compatible (identical or similar) schemas.
- Creates a single unified output table.
- Available visually in Power Query Editor.
Memory trick: Append queries to combine similar tables into one.