Retrieve values from many workbooks

This article explains how to consolidate data from numerous Excel files using Power Query.

The situation:

• 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.