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:

File: Sales data 2020 with tab: Sales per country, Sales per customer

File: Sales data 2019 with tab : Sales per country, Sales per customer

 

I want to append the data to Sales per country Total, and Sales per Customer Total. 

This I can do in Power query fairly ease because the columns names and make up of the tabs is always Identical. 

However it can happen that a 3rd 4rt etc tab is added in all excel files and I would like that Power Query automatically creates the 3rd and 4th Total table. 
eg: 

File: Sales data 2020 with tab: Sales per country, Sales per customer, Sales per person, Sales per city

File: Sales data 2019 with tab : Sales per country, Sales per customer, Sales per person, Sales per city

 

Can you automate Power Query in a way that it always appends all excel tabs which have the same name into a new total table over multiple Excel files in the same folder??

Need Help

1 Reply

  • 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