Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is integrating data from a legacy system into Power BI. The system exports transaction data into a series of CSV files, one for each month. Each CSV file has the same schema, and contains columns like 'TransactionID', 'TransactionDate', and 'Amount'. The analyst needs to combine all these monthly CSV files into a single table in Power Query. Which Power Query connector and subsequent action are most appropriate for this task?
- AFolder connector, then Combine & Transform Data.
- BText/CSV connector for each file, then Append Queries.
- CSQL Server connector to the file system, then UNION ALL.
- DWeb connector to a shared network drive, then Parse CSV.
Show answer & explanationAnswer & explanation
Correct answer: A. Folder connector, then Combine & Transform Data.
The Folder connector is specifically designed to access and combine multiple files within a directory, and its 'Combine & Transform Data' feature automates the process of loading and appending data from files with the same schema.
Why the other options are wrong
- B. This approach is tedious and inefficient for many files, requiring manual import and appending for each, rather than an automated solution.
- C. The SQL Server connector is for database connections, not for directly accessing and combining CSV files from a file system.
- D. The Web connector is for web-based data sources, not local or network file systems, and parsing CSV would still require a mechanism to combine them.
Folder Connector (Power Query)
A Power Query connector that allows users to import data from all files within a specified folder. It's especially powerful when combined with the 'Combine & Transform Data' feature to merge multiple files with the same schema.
- Connects to a directory on a local or network drive.
- Ideal for combining multiple files of the same type and structure.
- Automates data loading and appending using 'Combine & Transform'.
Memory trick: Folders collect, then Power Query combines like a master chef.