Dynamically Combine Multiple Sheets p# 1: Power Query




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:

Dynamically Combine Multiple Sheets p#2


Sample data tables

Data is on four different sheets.


Download Sample Data

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


Result

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

Dynamic Output


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.

Adding sheet for May 2020

Related article: Dynamically Combine Multiple Sheets p#2


@imvivran


Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING