Merge specific tabs excelfiles SharePoint Folder

[wdvro]

Best,

I am facing a specific problem.

Just some context: every Wednesday I receive via e-mail an excel (xlsx) file containing 3 tabs:
- Tab 1: table of contents
- Tab 2: effective dates: hotel occupancy for the next 90 days
- Tab 3: help

Every four weeks, however, a tab is added to that file. Then the file looks like this.
- Tab 1: table of contents
- Tab 2: actual dates: hotel occupancy for the next 90 days
- Tab 3: actual dates: hotel occupancy for the next 365 days
- Tab 4: help

The files arrive in my mailbox and are then automatically copied to a SharePoint folder via Power Automate.

Additional difficulty: the data provider occasionally changes the names of the tabs.

Anyone here to help me get the data related to hotel occupancy for the next 365 days (i.e. tab 3 in the file forwarded every 4 weeks) into the data model? How can I write this in Power Query?

Thanks in advance! 

[jgeddes · Accepted solution]

If you want to select the third tab from every file that has four tabs you could do something like...

add a column that gives the excel content for each file

= Table.AddColumn(PREVIOUSSTEP, "_excelContent", each Excel.Workbook([Content]))

clicking in either row in the '_excelContent' column will show the listing of sheets, tables etc for that file...

We are only concerned with 'Sheets' so I used a nested function to filter the nested tables to include only 'Sheets'...

fxSelectRows = 
    (inputTable as table) as table =>
    let
        source = inputTable,
        selectSheets = Table.SelectRows(inputTable, each [Kind] = "Sheet")
    in
        selectSheets

= Table.TransformColumns(PREVIOUSSTEP, {{"_excelContent", each fxSelectRows(_)}})

you now have only 'Sheets'

Now we only want rows in our table to include files with four tabs...

= Table.SelectRows(PREVIOUSSTEP, each List.Count(Table.ToRows([_excelContent])) = 4)

now we only want the third tab...

= Table.TransformColumns(PREVIOUSSTEP, {{"_excelContent", each Table.Range(_, 2, 1)}})

and you end up with...

from here you can combine the files into a single table and you are all set.