Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Hard
A data engineer is using a Dataflow Gen2 to ingest and transform data from multiple CSV files stored in a folder in Azure Data Lake Storage Gen2. All files in the folder have the same schema. The engineer wants to combine all these files into a single table, apply a common set of transformations, and then load the aggregated data into a Lakehouse. Which Power Query transformation function is most efficient for combining multiple files with the same schema from a folder?
- ACombine Binaries
- BMerge Queries
- CGroup By
- DAppend Queries
Show answer & explanationAnswer & explanation
Correct answer: A. Combine Binaries
The 'Combine Binaries' feature in Power Query (often accessed by clicking the 'Combine Files' button when connecting to a folder) is specifically designed to efficiently combine multiple files with the same schema, automatically creating a sample file and function to apply transformations consistently.
Why the other options are wrong
- B. Merge Queries is used to combine columns from two tables based on matching values, not to combine multiple files into one vertically.
- C. Group By is used for aggregation (summarizing data), not for combining multiple files into a single dataset.
- D. Append Queries combines rows from two or more tables into a single new table. While it can combine, 'Combine Binaries' is more efficient and automated for combining *files* from a folder source.
Power Query Combine Files
A feature in Power Query that simplifies combining multiple files with the same schema from a folder into a single dataset.
- Automates the creation of a transformation function.
- Applies transformations to all files consistently.
- Efficient for folder-based data sources.
Memory trick: Folder of files becomes one table with a single click.