Combine Excel Files from SharePoint Subfolders with Power Query
Question details
Automatically import and combine selected data from weekly roster files stored across various SharePoint subfolders into a unified dataset.
- Product
- Microsoft Excel, Power BI
- Device & OS
- not provided
- Scenario
- Aggregating multiple weekly roster Excel files located in different branch subfolders on SharePoint into one master dataset for analysis.
- Observed behavior
- Data is scattered across multiple locations on SharePoint, requiring an automated query to consolidate the files without manual copying and pasting.
Ensure you have the exact SharePoint site URL, appropriate access permissions, and that all target Excel files share identical table structures and column headers.
Use Power Query SharePoint Folder Connector
Connect directly to the SharePoint document library to filter and combine all required Excel files into a single table automatically.
By connecting to the root SharePoint folder, you can retrieve metadata for all files within its subfolders. Applying filters allows you to isolate exactly which files to combine based on file extension or folder path, ensuring only the correct rosters are processed.
Open Excel or Power BI, navigate to the Data tab, click Get Data, select From File, and choose From SharePoint Folder.
Input the root URL of your SharePoint site (do not include specific subfolder paths in this step) and click OK. Authenticate with your organizational account if prompted.
When the preview window appears, click Transform Data instead of Combine. Filter the 'Extension' column to only include '.xlsx' and filter the 'Folder Path' to include only your target branch subfolders.
Click the Combine Files icon (two down arrows) on the header of the 'Content' column. Select the specific worksheet or table structure to extract, and click OK.
Apply any additional data transformations, such as removing unnecessary columns, then click Close & Load to finalize your combined dataset.
Create a Dataflow in Power Query Online for Power BI
If your end goal is Power BI reporting, bypass the Excel master file entirely by using Power BI Dataflows in the cloud.
Experience Seamless Spreadsheet Management with WPS Office
While advanced SharePoint Power Query connections are tied to the Microsoft ecosystem, WPS Office provides a lightweight, highly compatible, and free alternative for managing complex daily spreadsheets, standard data consolidation, and formatting.
- 1. Download and Install: Visit the official WPS website to download and install WPS Office on your computer.
- 2. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel workbooks instantly without losing any formatting.
- 3. Analyze Data: Utilize familiar formulas, pivot tables, and data consolidation tools to streamline your daily reporting tasks.
Frequently Asked Questions
Can I choose specific subfolders instead of processing the entire SharePoint site?
Yes. When you first connect to the SharePoint folder, select 'Transform Data'. You can then apply text filters to the 'Folder Path' column to include only the specific branch subfolders you need before clicking the Combine Files icon.
Do all Excel files need to have the exact same structure to be combined?
Yes, for Power Query to automatically combine files seamlessly, each Excel file should have identical column headers and data types. If a new file has different column names, it may cause errors or result in null values for mismatched columns.
Why does the SharePoint Folder connector feel slow when refreshing?
The connector reads the metadata for all files in the document library before applying filters. If your SharePoint site contains thousands of files, this evaluation can take time. To optimize performance, apply your 'Folder Path' and 'Extension' filters as early as possible in your query steps.
