Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 42
A Power BI Data Analyst needs to combine three CSV files containing sales data from different regions (each with columns: OrderID, SaleDate, Amount, Region) into a single table for reporting. All files have the same structure and are stored in a folder. The analyst wants to minimize manual steps and ensure new files added to the folder are automatically included in future refreshes.
Which approach should the analyst use in Power BI?
-
A
Combine the CSV files into a single Excel file outside Power BI, import the Excel file, and create a report from the combined data.
-
B
Import each CSV file individually into Power Query, append them into a single table, and manually update the query when new files are added.
-
C
Import one CSV file, duplicate its query for each region, manually update the file paths, and append the queries into a single table.
-
D
Use the Folder connector in Power Query to import all CSV files from the folder, combine them into a single table, and enable data refresh.
Reveal correct answer
Correct answer: D
Explanation
Problem Breakdown
The analyst needs to combine three CSV files with sales data (OrderID, SaleDate, Amount, Region) from different regions, stored in a folder. The requirements are:
Combine the files into a single table for reporting.
Minimize manual steps.
Ensure new files added to the folder are automatically included in future refreshes.
Why This Configuration?
Folder Connector in Power Query:
The Folder connector in Power Query allows the analyst to import all CSV files from a specified folder into a single query. This is ideal for combining multiple files with the same structure (same columns: OrderID, SaleDate, Amount, Region).
Power Query automatically combines the files into a single table, appending their rows while preserving the column structure.
Automatic Inclusion of New Files:
When new CSV files are added to the folder, Power Query’s Folder connector automatically includes them in the query during the next refresh, meeting the requirement for future-proofing without manual updates.
Enable Data Refresh:
After combining the files in Power Query, the resulting dataset can be loaded into Power BI and published to the Power BI Service, where a refresh schedule can be configured to update the data as needed (e.g., when new files are added).
Why Not the Other Options?
Import Each CSV Individually and Append:
Importing each file separately and appending them manually is time-consuming and requires updating the query when new files are added, violating the requirement to minimize manual steps.
Duplicate Query and Update File Paths:
Duplicating a query for each region and manually updating file paths is inefficient and doesn’t automatically include new files, increasing maintenance effort.
Combine in Excel Outside Power BI:
Combining CSV files into an Excel file manually before importing into Power BI adds external steps, doesn’t support automatic inclusion of new files, and increases complexity.
Practical Considerations
Folder Setup: Ensure all CSV files are in the same folder and have identical column structures. Verify file naming conventions to avoid errors.
Power Query Steps:
In Power Query, select Get Data > From Folder, choose the folder, and use the Combine Files option.
Review the sample file transformation to ensure columns are correctly mapped.
Refresh Configuration: In the Power BI Service, configure a refresh schedule if the folder is accessible (e.g., in a cloud location like OneDrive or SharePoint). For local folders, set up an on-premises data gateway.
**igliano: Data Quality: Check for inconsistent data formats or missing columns across CSV files to prevent errors during combining.
References:
A.
Combining files into an Excel file outside Power BI adds manual steps and doesn’t support automatic inclusion of new CSV files, making it inefficient for ongoing refreshes.
B.
Importing each CSV file individually and appending them in Power Query works but requires manual updates when new files are added, failing to meet the requirement for automatic inclusion of new files.
C.
Duplicating queries and manually updating file paths is labor-intensive and error-prone, and it doesn’t automatically include new files, violating the requirement.
D.
Using the Folder connector in Power Query automatically imports all CSV files from the specified folder and combines them into a single table. This approach supports automatic inclusion of new files during data refresh, minimizing manual steps.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
