top of page
Search
    • 1 min read

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:

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.



Comments

bottom of page