How to Pull Data from Multiple Excel Workbooks Based on a Selected Date
Question details
The user needs to import and combine data from multiple Excel workbooks and filter the resulting records based on a specific date selected in a master workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating daily or date-specific reports from various source files into a single master workbook automatically.
- Observed behavior
- A structured method is required to automatically pull, append, and filter data across multiple files that share the same date field structure.
Ensure all source workbooks are stored in a single dedicated folder and share a consistent column structure, particularly for the date and value columns.
Use Power Query 'From Folder' to Append and Filter Data
The most efficient way to combine multiple files and filter by date is using the 'From Folder' connector in Power Query.
This method involves pointing Power Query to a specific directory containing all your source files. It will automatically extract and append the data from every workbook in that folder, allowing you to clean and filter the consolidated data in one go.
Place all the source Excel workbooks you want to import into a single dedicated folder on your computer or network drive.
In your master Excel workbook, navigate to the Data tab on the ribbon, click 'Get Data', select 'From File', and then choose 'From Folder'. Browse to your designated folder and click OK.
In the preview window that appears, click 'Combine & Transform Data'. Select the specific sheet or table name that exists across all your source files, then click OK to open the Power Query Editor.
Locate your date column in the Power Query Editor. Ensure it is formatted correctly by clicking the data type icon in the column header and selecting 'Date'.
Click the filter dropdown arrow on your Date column, select 'Date Filters', and choose 'Equals'. Enter the specific date you need to pull.
Click 'Close & Load' on the Home tab. The combined and filtered data will now be imported into a new worksheet in your master workbook.
Create a Dynamic Date Parameter in the Master Workbook
To allow users to select a date in a cell and automatically update the Power Query results, you can create a dynamic parameter.
A Lightweight Alternative for Your Data Processing Needs
While Power Query is a built-in feature of Microsoft Excel, WPS Office provides a free, lightning-fast alternative with excellent format compatibility and an intuitive interface for your daily spreadsheet tasks.
- 1. Download WPS Office: Visit the official WPS website and download the free installer package for your operating system.
- 2. Install the Software: Run the downloaded file and follow the straightforward on-screen prompts to complete the installation process.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheet and easily open your existing Excel workbooks to continue your work seamlessly.
Frequently Asked Questions
Can Power Query pull data from subfolders?
Yes, when you use the 'From Folder' connector, Power Query automatically pulls files from both the main folder and any subfolders within it. You can filter the 'Folder Path' column in the editor to exclude specific subfolders if needed.
What happens if my source workbooks have different sheet names?
If the sheet names vary across files, the standard combine process might fail. You can bypass this by editing the 'Transform Sample File' step in Power Query and navigating to the sheet via its Item Index (e.g., the first sheet) rather than its specific Item Name.
How do I update the data when new files are added to the folder?
Simply go to the Data tab in your master workbook and click 'Refresh All'. Power Query will automatically scan the folder, detect the newly added files, apply all your transformation steps, and update the final table.
Why am I getting a 'Data Format Error' when combining files?
This error usually occurs if the date or value columns in one of your source files contain text, blank spaces, or incompatible characters instead of actual numbers or dates. Check your source files to ensure all data types are consistent before appending.
