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.