Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
wdvro
Frequent Visitor

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! 

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

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

jgeddes_0-1708459479578.png

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...

jgeddes_1-1708459599861.png

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'

jgeddes_2-1708459770691.png

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

 

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

 

 

jgeddes_4-1708459871197.png

now we only want the third tab...

 

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

 

and you end up with...

jgeddes_5-1708459943153.png

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

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

1 REPLY 1
jgeddes
Super User
Super User

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

jgeddes_0-1708459479578.png

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...

jgeddes_1-1708459599861.png

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'

jgeddes_2-1708459770691.png

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

 

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

 

 

jgeddes_4-1708459871197.png

now we only want the third tab...

 

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

 

and you end up with...

jgeddes_5-1708459943153.png

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

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors