
Case: When the source tables and the output are in the same workbook
The underlying assumption is that all sheets have the same data table structure.
For source table different than the output table, refer to the article:
Dynamically Combine Multiple Sheets p#1
Sample data tables
Data is on four different sheets.

Pre-requisite: All data tables must be either Excel tables (CTRL+T) or Name Ranges (CTRL+F3)
Tip: Add a common keyword while defining the table or name range. For example, data_week1, data_week2, so on and so forth. It helps in filtering the right tables before combining them.

Combining all tables
Data > From Other Sources > Blank Query

It opens a blank query. In the formula bar, write the following expression:
= Excel.CurrentWorkbook()

Result

Adding Filter Step
We can specify the type of tables we wish to combine. In this example, we want to combine all tables with names starting with data.
Filter > Text Filters > Begins With


Expand the table contents

Output:

Data Transformations
Perform requisite data transformations:
· Change the data type
· Transform the Date into Start of Month

Close & Load (To)
We can summarize all the tables using Pivot.


Optional: Check the option when we want to analyze the data using DAX modeling in Power Pivot.

Any update automatically adds to the Pivot.

Related article: Dynamically Combine Multiple Sheets p#1