Dynamically Combine Multiple Sheets p# 1: Power Query

Case: When the source data is in a different workbook than the output table

The underlying assumption is that all sheets have the same data table structure.

For source table same as the output table, refer to the article:

Dynamically Combine Multiple Sheets p#2

Sample data tables

Data is on four different sheets.

Download Sample Data

Step 1: Get Data

Data > Get Data > From File > From Workbook

Select one sheet > Transform Data

Step 2: Delete all steps except Source

Step 3: Right-click Data column > Remove other columns

Step 4: Expand the Data column.

Step 5: Adjust Headers

Home > Use the First Row as Header

Filter out the remaining header rows.

Right-click > Filters > Does Not Equal

Result

Include Sheet Name

In case we need to include sheet name in the final output, make the following adjustment in Step 3.

Select Name + Data column > Right Click > Remove Other Columns

Dynamic Output

Load the query output to a pivot table.

Home > Close & Load To > Pivot Table

Any change in the workbook, Power Query updates the output accordingly.

Adding sheet for May 2020

Related article: Dynamically Combine Multiple Sheets p#2

@imvivran