To collect data from many Excel workbooks, use Power Query’s From Folder connector for files stored locally, or the SharePoint Folder connector for work or school files in SharePoint or OneDrive. Combine the intended files, then choose whether to append their rows into one table or merge tables to retrieve fields for matching keys. A saved query can be refreshed when its source files change.
Choose between stacking records and looking up matches
First decide what the finished result should contain. Power Query’s folder connector gathers data from multiple files; append and merge determine how tables relate in the result.
As an Amazon Associate I earn from qualifying purchases.
| Your goal | Operation | What it does |
|---|---|---|
| Put records from similarly structured workbooks into one longer table | Append | Adds rows from one query after another. Columns align by their names, so a missing column can result in null values. |
| Find rows with the same key and bring selected fields across | Merge | Matches tables on a common column value; expand the columns you want from the matching table. |
For example, if every monthly workbook contains the same kind of transaction records and you want them all in one list, combine the files and append the rows. If one table has employee IDs and you want to bring in each matching employee’s department from another table, merge on the ID and expand the department field.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPrepare the workbooks before combining them
Folder combine works best when files have a consistent structure: the same column headers, data types and number of columns. Column order can differ because Power Query matches columns by name. Each workbook may contain multiple sheets, tables or named ranges, so confirm that the selected object is the one you intend to extract.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
- Put only the intended source workbooks in a dedicated folder, or carefully filter the file list before combining.
- Check that the files use consistent headers and compatible data types.
- Decide which sheet, table or named range should be extracted from each workbook.
- If layouts or workbook objects vary, inspect and standardize the sources before depending on one combined result.
Power Query uses a sample file to establish the extraction steps and applies those steps to the other files. An unrelated workbook in the folder, or a change to the source structure, can therefore affect the combined output. Microsoft explains the sample-file process and schema expectations in its combine-files overview.
Combine files from a local folder
- Place the source workbooks in a folder reserved for this query.
- In Excel, select Data > Get Data > From File > From Folder.
- Review the listed files. Filter by extension or folder path if needed so only the intended files are included.
- Choose the combine option and use the sample-file and navigation experience to select the sheet, table or other workbook object to extract.
- In Power Query, check the preview, make any needed transformations, and load the result.
Microsoft’s Folder connector documentation describes the connector and its combine workflow. Once the files are combined, append or merge as appropriate for the result you chose.
Combine SharePoint or OneDrive work files
For files in SharePoint or OneDrive for work or school, use the SharePoint Folder connector to access the target location and combine the relevant files. Check the contents and filter to the intended folder and files before combining. If you need only one workbook, select that file’s binary value to navigate into it rather than combining the folder. Microsoft notes that SharePoint.Contents can be optimal in environments with a large number of files.
See Microsoft’s SharePoint Folder connector guidance for connection and navigation details.
Retrieve data from just one workbook
If you are importing from a single file rather than many, use Data > Get Data > From File > From Excel Workbook, browse to the workbook and select the intended object in Navigator. Load it directly or choose to transform it in Power Query first. For multiple workbooks, Microsoft recommends a multi-file connector such as Folder or SharePoint Folder; see its Excel workbook connector documentation.
Refresh the combined result
Power Query stores the transformation steps and runs them again when you refresh, bringing in changes, additions and deletions from external sources. Refresh after source files change, and review the result if a file or workbook structure has changed: the folder-combine process applies the sample-file extraction steps to each file. Microsoft describes the refresh behavior in its Power Query refresh documentation.
Rank #4
Check platform and file-format requirements
Power Query is available in Excel for Windows, Mac and the web, but capabilities can vary by host and deployment. Menu labels and behavior may also differ by version. Consult Microsoft’s Power Query overview for platform details and confirm the steps in the Excel environment you use.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsLegacy .xls and .xlsb workbooks can require the Access Database Engine OLEDB provider. Microsoft says that provider cannot be installed in cloud service environments; a gateway with ACE may be needed there. Check the current Excel connector requirements if those formats or cloud-hosted processing apply to your workflow.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
