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
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 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 |
Click on target cell E2 and enter your formula above.Shortcut: Click "Load Solution" to inspect