Excel
All Top 50 Excel & Business Modeling Questions#37
Consolidating Multiple Workbooks Automatically with Power Query Append
MediumPwCInterview Question #37
Asked at PwCEvery month, 20 branch offices email a separate sales workbook named `Sales_BranchName_Month.xlsx`. An analyst previously spent 4 hours copy-pasting them into a master sheet. Explain how to configure Power Query's 'Get Data > From Folder' to automate this permanently.
Input Table: FolderStructure
3 rows preview| Folder Path | Files Inside |
|---|---|
| C:/MonthlyReports/ | Branch_North_Jan.xlsx |
| C:/MonthlyReports/ | Branch_South_Jan.xlsx |
| C:/MonthlyReports/ | Branch_East_Jan.xlsx |
Expected Output Structure1 rows
| Master Dataset (Consolidated) | Rows Combined | Refresh Time |
|---|---|---|
| Single unified table in Data Model | All 20 branches appended | One click: Data > Refresh All (5 seconds) |
Interview Context
Asked frequently in data analyst and business analyst technical rounds. Focus on clean filtering, optimal indexing usage, and unambiguous column selection.
Microsoft Excel 365
E2fx
← Swipe the sheet to see more columns →
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Folder Path | Files Inside | ||||||||
| 2 | C:/MonthlyReports/ | Branch_North_Jan.xlsx | Target [Enter Formula] | |||||||
| 3 | C:/MonthlyReports/ | Branch_South_Jan.xlsx | ||||||||
| 4 | C:/MonthlyReports/ | Branch_East_Jan.xlsx | ||||||||
| 5 | ||||||||||
| 6 | ||||||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
This question demonstrates a technique that can't be graded as a single cell formula (it needs another sheet, a real PivotTable, or compares two methods side-by-side). Read the explanation below instead.