Merge data from multiple excel files from multiple sharepoint folders

You’re actually closer than it might seem — Power Query can still handle this, even across multiple SharePoint folders.

Instead of pointing to a single folder, you can connect to the SharePoint site itself using the SharePoint Folder connector. That will return all files across the site, including subfolders.

From there, you can:

Filter down to the specific folders or file name patterns you care about
Expand the “Content” column to access each file
Apply your existing transformation logic to the table inside each workbook
This way you’re essentially treating all files as one combined source, even though they’re spread across multiple folders.

The main challenge tends to be consistency — as long as the table structure is the same across those files, the merge should work pretty smoothly.

hi Badjdav 

You’re thinking about this too narrowly—Power Query can absolutely handle multiple subfolders, you just need to stop pointing it to a single folder and instead connect at the site level using SharePoint.Contents or even better SharePoint.Files. These connectors return all files across the entire SharePoint site (including all subfolders) in one table, after which you simply filter by folder path, file name pattern, or extension to isolate your 30+ folders and the specific Excel files you need. From there, you use the standard “Combine Files” approach to extract the table from each workbook and append everything into one consolidated dataset. The key shift is this: don’t try to loop through folders manually—let Power Query pull everything at once and then filter down, which is exactly what it’s designed to do.

Hi Badjdav ,
Thank you for the detailed explanation.
Based on your scenario, this can be achieved using a single Power Query, and there is no need to create separate queries for each file.

You can connect using Get Data → SharePoint Folder, provide the SharePoint site URL, and make sure to select Transform Data instead of Combine. This will return a full list of files across the site, including all subfolders, which is the expected behavior as per Microsoft documentation:
https://learn.microsoft.com/power-query/connectors/sharepoint-folder

Once the data is loaded, you will see a table containing all files (around 700 in your case). At this stage, you should filter the dataset to keep only the required files by using columns such as Folder Path, Extension (.xlsx), and Name (based on your file naming pattern). Filtering before combining is the recommended approach and ensures that only relevant files are processed.

After filtering down to your required files, you can then work with the Content column. By expanding it and selecting Excel.Workbook, you will be able to access the tables or sheets inside each file. From there, filter to the correct table (or sheet) that is consistent across all files and expand the Data column to combine everything into a single consolidated table.

The reason your required files were not appearing earlier is because the Combine & Transform option uses a limited sample of files before any filtering is applied. This behavior is expected, and using Transform Data first allows you to control which files are included before applying the combine logic:
 

The correct approach is to connect at the SharePoint site level, filter the required files, and then expand and combine them, which will consolidate all your data into one query.

Please let us know if you need any further assistance.

Best Regards,
Community Support