Merge data from multiple excel files from multiple sharepoint folders

[Badjdav]

I have 1 excel template file being completed across 30+ sub folders on a single SharePoint.   Each sub folders contain 2 copies of the excel (individually named)

I want to merge the data from the table in each seperate excel file from the individual SP folders into 1 consolidated excel table.

If it were a single SP folder then PowerQuery should suffice but with it being mutltiple SP folders, I'm not sure where to start

[donbuser · Accepted solution]

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.

[Poojara_D12 · Accepted solution]

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.

[Badjdav]

Thanks for your reply.  Its not quite solved for me as I have have entry level experience in Power Query Editor

So in a new Power Query I select "Get Data", select "Sharepoint folder", enter the homepage of the Sharepoint site (removing the "/SitePages/HomePage.aspx" from the URL).

Then I get the option to Combine, Load or Transform.   

When selecting Combine & Transform i get a sample list of all files on the Sharepoint but not the files I need to transform (think it shows around 50 of the 700 filles in the sample dropdown) and the files I need to transform are not in the sample list.

When selecting Transform i get list of all files on the Sharepoint but this seems to be a table list of files, how do I select the file I want to transform to get to the table contained within the file to transform?

Normally I would use "Get Data",select "Web" and use the path address of a single excel file and select Transform create the query. 

So I need to combine about 54 files out of the 700+ files on SP.  Do I have to do a single query per file and append or can it be done in a single query?

[v-menakakota]

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

[donbuser · Accepted solution]

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.

[Badjdav]

Thanks for your reply.  Its not quite solved for me as I have have entry level experience in Power Query Editor

So in a new Power Query I select "Get Data", select "Sharepoint folder", enter the homepage of the Sharepoint site (removing the "/SitePages/HomePage.aspx" from the URL).

Then I get the option to Combine, Load or Transform.   

When selecting Combine & Transform i get a sample list of all files on the Sharepoint but not the files I need to transform (think it shows around 50 of the 700 filles in the sample dropdown) and the files I need to transform are not in the sample list.

When selecting Transform i get list of all files on the Sharepoint but this seems to be a table list of files, how do I select the file I want to transform to get to the table contained within the file to transform?

Normally I would use "Get Data",select "Web" and use the path address of a single excel file and select Transform create the query. 

So I need to combine about 54 files out of the 700+ files on SP.  Do I have to do a single query per file and append or can it be done in a single query?

[v-menakakota]

Hi @Badjdav ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team

[OwenAuger]

Hi @Badjdav 

There are different methods you can set up such a query.

However I personally like the "Get all files from folder" function created by @ImkeF, published here.

This function returns a table containing all files within a specified SharePoint Online folder, including subfolders.

To use this function, you need to

1. Copy this function into Power Query as a standalone query. I'll assume it is named GetAllFilesInFolder.

2. Within this function, add a definition for RootPath, being the root path of the relevant SharePoint site. It must be hard-coded, not passed in as a parameter.

For example, after editing, the first few lines of the function could look like this:

let
  RootPath = "https://MyTenant.sharepoint.com/sites/MySite",
  func = 
  // fnGetAllFilesInSharepointFolder
    (FullPath as text) => 
    let 
      // Helper function
      fnUriUnescapeString = 
      //Source: https://stackoverflow.com/questions/36242695/how-to-decodeuricomponent-ex-2f3f263d
        (data as text) as text => 
// etc.

3. Invoke the function with the full path passed as an argument:

= GetAllFilesInFolder("https://MyTenant.sharepoint.com/sites/MySite/MyFolder1/MyFolder2/")

4. After invoking the function, select the Combine files option on the Content column:

5. Follow the usual process for specifying a sample query to apply to each file.

Attached is a PBIX with the queries set up with placeholder tenant URL and folder path.

Would something like this work for you?

[v-menakakota]

Hi @Badjdav ,

Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to thank @OwenAuger   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team