Dynamically Append Multiple Sheets p# 2: Power Query

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.

Download Sample File

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

@imvivran