Combine files in different folders that have a specific tab

[nok]

Hello!
I have different Excel files in different folders, all of them within a Sharepoint folder called "Root Folder". All Excel files have the name "Presentation.xlsx", but some of them have a tab called "Level" and others do not.

I want to combine all Excel files within the subfolders of the Root Folder, but only those that have the "Level" tab (because this is the tab I will use in BI). The ones that do not have this tab are causing an error when I try to combine all of them.

Is it possible to do this? A combination of Excel files that are in several different folders within my Root Folder, where they all have the same name but some of them have the "Level" tab?

[lbendlin]

yes, that is possible in Power Query.  What have you tried and where are you stuck?

[nok]

Hello @lbendlin 
I tried to combine all files named "Presentation.xlsx" that are inside any subfolders of"Root Folder" and when appeared the option to select the file on which I would base the combination, I selected a file that I know has the "Level" tab and I also selected the "Level" tab to combine.

However, the combination only gets the data from the first file and then gives an error (probably because it must also be trying to get the "Level" tab of files that do not have this tab).

How can I make this distinction, in Power Query, so that BI tries to combine only the files that have the tab named "Level"?

[lbendlin · Accepted solution]

Here is the general approach.  Please study the code. I attached the folder structure for my test.

let
    Source = Folder.Files("C:\Users\xxx\Downloads\Test"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "Presentation.xlsx")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content", "Folder Path"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.PromoteHeaders(Table.SelectRows(Excel.Workbook([Content]), each ([Name] = "Level")){[Item="Level",Kind="Sheet"]}[Data], [PromoteAllScalars=true])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Errors", "Custom", {"Column"}, {"Column"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Custom",{"Folder Path", "Column"})
in
    #"Removed Other Columns1"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.