Dynamically Combine Multiple Sheets p# 1: Power Query

One of the key tasks as an analyst is to combine and transform data, before we start analyzing it. Power Query is very handy in such situations.

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 Append Multiple Sheets p# 2: Power Query

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.

Dynamically Append Multiple Sheets p# 2: Power Query

Combining multiple files in a Folder: Power Query

I write about MS Excel, Power Query, Power BI, Power Pivot, DAX, Data Analytics, and sometimes travelling.

@imVivRan