A Power BI report requires sales data from a legacy system that exports monthly data into separate Excel files. Each Excel file contains multiple sheets, but only the sheet named 'Sales Summary' is relevant. The 'Sales Summary' sheet in each file has the sales data starting from row 5, with the actual headers in row 4. Additionally, the first three columns of the 'Sales Summary' sheet (e.g., 'Region', 'Date', 'Product') contain metadata that should be kept, but the remaining columns represent monthly sales figures (e.g., 'Jan-2023', 'Feb-2023') which need to be unpivoted to a single 'Month' column and a 'SalesAmount' column. What is the most efficient sequence of Power Query transformations to achieve this for all files in a folder?
- A1. Get data from Folder. 2. Combine & Transform Data. 3. Promote Headers. 4. Remove Top Rows (4 rows). 5. Filter for 'Sales Summary' sheet. 6. Unpivot Other Columns.
- B1. Get data from Folder. 2. Combine & Transform Data. 3. Filter for 'Sales Summary' sheet. 4. Promote Headers. 5. Remove Top Rows (4 rows). 6. Unpivot Other Columns.
- C1. Get data from Folder. 2. Combine & Transform Data. 3. Filter for 'Sales Summary' sheet. 4. Remove Top Rows (3 rows). 5. Use First Row as Headers. 6. Unpivot Other Columns.
- D1. Get data from Folder. 2. Combine & Transform Data. 3. Remove Top Rows (3 rows). 4. Use First Row as Headers. 5. Filter for 'Sales Summary' sheet. 6. Unpivot Other Columns.
Show answer & explanationAnswer & explanation
Correct answer: C. 1. Get data from Folder. 2. Combine & Transform Data. 3. Filter for 'Sales Summary' sheet. 4. Remove Top Rows (3 rows). 5. Use First Row as Headers. 6. Unpivot Other Columns.
The correct sequence starts by combining files from the folder, then filtering for the specific sheet. After that, removing the initial metadata rows (3 rows above headers) is crucial. Then, promoting the actual header row (row 4 becomes the new header) and finally unpivoting the monthly sales columns will yield the desired structure.
Why the other options are wrong
- A. Promoting headers before filtering for the sheet or removing top rows would be incorrect. The order of operations is critical.
- B. Promoting headers before removing top rows would use incorrect headers. Also, removing 4 rows after promoting headers would remove the actual data.
- D. Removing top 3 rows before filtering for the sheet might cause issues if the sheet names are in those rows, and promoting headers after removing 3 rows would still use the wrong row as headers if the actual headers are in row 4.
Complex Excel Data Preparation
A multi-step Power Query process involving combining files, selecting specific sheets, handling header rows, and unpivoting data to transform messy source data into a clean, tabular format.
- Order of operations is critical for correct transformation.
- Folder connector simplifies combining multiple files.
- Remove Top Rows and Promote Headers are key for non-standard data layouts.
- Unpivot is essential for converting column-based attributes into rows.
Memory trick: Folder's combined, sheet is found, rows removed, headers crowned, then unpivot all around.