Showing results for 
Search instead for 
Did you mean: 
Helper IV
Helper IV

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

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

Community Support
Community Support

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:



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.

Super User
Super User

Hi @smjzahid 


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


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


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

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

Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors