Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Dynamically append Excel tabs from multiple Excel files from folder

HI All,   I  have a folder in which Excel files are periodically saved. These excell files contain multiple tabs of data per year and every Excel file is 100% identical to one and other. Example: ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

 

Not sure how you did for your other two Total tables, this is the one I normally use to read from Folder. Prior to promote header, you can specify your condition to select Name/Item/Kind/Hidden, combine all tabs you want. And you can re use it and modify the condition to read all other tables with the same conditions.

 

let
    Source = Folder.Files("C:\Users\yourFolderPath"),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders( Table.SelectRows( Excel.Workbook([Content]), each [Name]="Sales per person")[Data]{0})),
    Custom1 = Table.Combine( #"Added Custom"[Custom])
in
    Custom1