top of page
Search
    • 1 min read

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:


Sample data tables

Data is on four different sheets.


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



bottom of page