7 April 2017
This article explains how to consolidate data from numerous Excel files using Power Query.
- A folder contains hundreds of Excel files.
- New workbooks can be added to the folder, or some workbooks can be removed.
- Workbooks do not contain the same number of worksheets.
- Worksheets may be added or removed at any time.
- There is no naming convention for the worksheets.
- Each worksheet may contain one or more values to retrieve.
- The data needs to be consolidated often.
The solution: Use Power Query to import and consolidate the data automatically.