cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smjzahid
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

smjzahid_0-1624282353867.png

Excel File 2 has below tabs

smjzahid_1-1624282411371.png

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

4 REPLIES 4
v-stephen-msft
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.

excel-consolidate.png

 

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.

Vera_33
Super User
Super User

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

 

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:

 

smjzahid_0-1624285738835.png

 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

 

image.png

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

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors