How to combine the first tab of each xls file using Combine File Function

[smjzahid]

I have mutliple Excel file ( each file has 5 to 6 different tabs). However, I want to combine the first tab of each file only into 1 single logical file. (The data structure for all files )

for eg: 

Excel File 1 has these tabs

Excel File 2 has below tabs

I want to only combine the Week 1, Week 2 and son tabs only from individual files. How do I achieve this in Power Query

[v-stephen-msft]

Hi @smjzahid ,

Based on my research, I found the workaround.

The entire operation is merged in Excel first, and then you only need to connect to the integrated Excel.

You need to Run Excel Consolidate in Excel. For more details, please refer to Consolidate data in Excel and merge multiple sheets into one worksheet.

 

Overall, there are other four ways to merge Excel worksheets into one without copying and pasting:

• Combine sheets with Ultimate Suite
• VB scripts
• Power Query for Excel
• Merge two Excel sheets into one by the key column(s)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vera_33]

Hi @smjzahid 

Put all Excel files in a folder, read from it, basical find the [Item] contains "Week"

let
  Source = Folder.Files("C:\Users\yourFolderPath"), 
  AddCustom = Table.AddColumn(
    Source, 
    "Custom", 
    each Table.PromoteHeaders(
      Table.SelectRows(Excel.Workbook([Content]), each Text.Contains([Item], "Week"))[Data]{0}
    )
  ), 
  Custom1 = Table.Combine(AddCustom[Custom])
in
  Custom1

[smjzahid]

Hi @Vera_33 

Thanks for your prompt reply, cant put all files in 1 single folder. The team that owns data must split the files in separate folders to keep it organised, and i dont have permission to copy the files and place those in 1 single folder. they need to be like that only for eg:

 and then every Period has 8 to 10 different xls files (in which I only need to combine the very first tab) as shown in my post above)

for eg; PERIOD 1 has 7 different files as shown below

and so on, is ther a workaround to achieve this, without disturbing the folder structure please.

Thanks for your help

[Vera_33]

Hi @smjzahid 

Do you have all these folders in one folder? I mean you have a folder containing Period 1 folder, Period 2 folder, etc. Then read from this folder, it can read all the files in the sub folders, then you can try to locate items contains "Week" in name