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?

[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