1 min
Updated: Aug 31, 2020
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:
Data is on four different sheets.
Data > Get Data > From File > From Workbook
Select one sheet > Transform Data
Home > Use the First Row as Header
Filter out the remaining header rows.
Right-click > Filters > Does Not Equal
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.