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

[Poojara_D12]

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.

[donbuser]

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.

[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