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

[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?