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:
Sample data tables
Data is on four different sheets.
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
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
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.
Related article: Dynamically Combine Multiple Sheets p#2