Summary of Combine Multiple Excel Sheets with a Transform Sample Sheet in Power Query

The video provides a detailed tutorial on how to combine multiple Excel sheets within a single workbook using Power Query by creating a "Transform Sample Sheet"—a concept inspired by Power Query’s automatic creation of a transform sample file when combining multiple files from a folder. This method allows users to apply transformations once on a sample sheet and have those changes automatically propagate to all other sheets before combining them.

Key Technological Concepts and Features:

Power Query’s Folder Combine Feature: Automatically creates a transform sample file that applies transformations to all files in a folder.
Transform Sample Sheet Concept: Extends this idea to multiple sheets within a single Excel workbook.
Parameter Creation: A parameter is created to hold data from the first worksheet, which is then used as a dynamic input for transformations.
Custom Function Creation: The Transform Sample Sheet query is converted into a function that can be applied to each worksheet’s data.
Dynamic Data Transformation: Transformations (like removing rows, deleting columns, promoting headers, filtering, and unpivoting) are applied only to columns common across all sheets to avoid errors.
Applying the Function: The Custom Function is invoked on each worksheet’s data column, effectively applying the same transformation logic to all sheets.
Dynamic Column Names: The M code is edited to avoid hardcoding column names, instead dynamically referencing column names from the Transform Sample Sheet to maintain flexibility.
Regional Date Formatting: Data types are applied with locale-specific settings to ensure consistent date formats regardless of user region.

Step-by-Step Process Highlighted:

Import all sheets from the workbook into Power Query.
Filter to retain only relevant sheets (those starting with "data").
Create a parameter pointing to the first sheet’s data.
Create a blank query that references this parameter and convert it into a function.
Build the Transform Sample Sheet by applying necessary transformations (remove top rows, delete columns, promote headers, filter out totals, unpivot columns).
Apply the Custom Function to all sheets.
Combine the transformed sheets into a single query.
Adjust M code to dynamically reference column names.
Test changes by modifying the Transform Sample Sheet and observe propagation across all sheets.
Apply appropriate data types and locale settings.

Benefits and Use Cases:

Centralized transformation logic for multiple sheets.
Avoids repetitive manual transformations.
Ensures consistency across datasets.
Useful for workbooks with multiple sheets representing different time periods or categories.
Enhances maintainability and scalability of Power Query workflows.

Additional Notes:

The tutorial encourages downloading an example file to follow along.
Emphasizes understanding the process through repetition.
Suggests further learning through related advanced Power Query videos.

Main Speaker:

The tutorial appears to be presented by an experienced Power Query user or educator (name not specified in subtitles). The style is instructional with step-by-step guidance.

Summary:
This video tutorial demonstrates how to replicate Power Query’s folder combine transform sample file functionality for multiple sheets in a single Excel workbook by creating a Transform Sample Sheet and a Custom Function. This enables centralized, reusable transformations applied dynamically to all sheets before combining, improving efficiency and consistency in data preparation workflows.

Notable Quotes

— 08:12 — « This means that power query will hardcode the item and the size column names but not any of the date column names. »

— 12:45 — « If you like this video, then why not subscribe and get notifications so you don't miss any of our future videos. And if you're looking for some more advanced Power Query techniques, why not check out this video next? I think it's another one you'll really enjoy. »