Forum Discussion

wdvro's avatar
wdvro
Frequent Visitor
2 years ago
Solved

Merge specific tabs excelfiles SharePoint Folder

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! 

Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    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.

     

1 Reply

  • jgeddes's avatar
    jgeddes
    Super User
    2 years ago

    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.