Power Query Masterclass: Advanced Data Cleaning & Automation

In this lecture, we introduce Module 1 and build an intuitive understanding of what Power Query does.

In this lecture, you’ll learn the ETL framework — Extract, Transform, and Load — which is the core concept behind how Power Query works.

We’ll break down each stage of ETL and explain how Power Query fits into this framework, giving you a clear mental model of how data is processed from raw source to final output

In this lecture, you’ll learn how to connect Power Query to data sources.

We’ll walk through how to bring data into Power Query from Excel files and explain what happens during the “Extract” stage of ETL.

This lecture is a guided walkthrough of the Power Query Editor.

You’ll get familiar with the interface, key panels, menus, and views, so you feel comfortable navigating the environment where all transformations are designed and managed.

In this lecture, we perform some basic data transformations using the Power Query interface.

You’ll see how common cleanup tasks — such as removing columns, handling simple issues, and shaping data — are applied during the Transform stage of ETL.

The goal here is awareness and familiarity, not memorization.

This lecture introduces an important mental model for working with Power Query.

You’ll learn how every transformation is recorded as an Applied Step, how Power Query evaluates these steps sequentially, and why the order of steps matters.

We’ll also introduce the ideas of precedent and dependent steps, which become critical when designing reliable queries later in the course.

In this lecture, we focus on the Load stage of ETL.

You’ll learn how to load transformed data back into Excel or Power BI, explore different load options, and understand when to use each option depending on your reporting or analysis needs.

In this final lecture of Module 1, we summarize what you’ve learned so far.

We’ll quickly recap the ETL framework, the Power Query Editor, basic transformations, and the importance of step sequencing. This lecture also sets expectations for the next module, where we begin working more deeply inside the Power Query Editor.

This lecture focuses on text transformations in Power Query.

You’ll learn how to clean and reshape text data using functions such as Trim, Clean, Extract and Change Case — techniques that are commonly required when working with messy, real-world data

In this lecture, you’ll learn how to split columns in Power Query.

We’ll cover splitting by delimiters and other advanced options.

This lecture introduces one of the most important foundational skills in Power Query: data types and error handling.

You’ll learn how Power Query interprets data types, how errors occur, and how to manage them effectively. Understanding this topic is critical for building reliable and predictable transformations.

In this lecture, you’ll learn how to replace values and handle missing data using Fill Down and Fill Up operations.

These techniques are especially useful when working with incomplete datasets, repeated values, or structured data that requires normalization.

This lecture covers Group By transformations in Power Query.

You’ll learn how to group data and perform aggregations such as sums, counts, and other calculations, allowing you to summarize and reshape data efficiently within the Power Query Editor.

In this lecture, we load the transformed data back into Excel.

You’ll also learn how to test and validate the results of your transformations to ensure the output matches expectations before moving forward.

In this final lecture of Module 2, we summarize what you’ve learned so far.

We’ll reflect on how the Power Query user interface can be used effectively for common transformations, and set the stage for the next module, where we start working with more complex, cross-table scenarios and explore where UI-based approaches can run into limitations.

In this lecture, we introduce Module 3 and explain why combining data across tables and files introduces new challenges. We’ll also set expectations for the common pitfalls you’ll encounter and why understanding them is critical.

This lecture covers the fundamentals of appending data in Power Query. You’ll learn how to combine multiple tables with the same structure and understand when append is the right approach.

In this lecture, we explore a common append issue called split column error. You’ll see how UI-based steps can introduce errors in data when there is change in source data.

This lecture demonstrates how renaming columns can create problems during query refreshes. You’ll learn why this happens and how small structural changes can impact downstream queries.

In this lecture, we examine the skip column pitfall and how it affects append operations. You’ll see why skipping columns can lead to fragile queries when data structures change.

This lecture shows how to consolidate data from multiple files in a folder using Power Query’s user interface. You’ll learn how folder-based workflows are created and when they are useful.

In this lecture, we take a deeper look at the queries automatically generated during folder consolidation. You’ll understand how Power Query structures these queries and why this matters for reliability.

This lecture revisits the skip column pitfall through a practical example. You’ll see how real-world data changes can cause queries to fail or behave unpredictably.

In this lecture, you’ll learn the basics of merging tables in Power Query. We’ll cover how merges work conceptually and when merging is right technique to combine data.

This lecture explores common merge-related pitfalls, including issues caused by data mismatches, duplicates in the mapping table and missing identifiers.

In this lecture, we review the different merge types available in Power Query. This is a conceptual overview to help you understand how each merge type behaves.

This lecture introduces the concept of tall versus wide data structures and explains why understanding table shape is critical when designing scalable Power Query solutions.

In this lecture, you’ll learn two common approaches to unpivoting data: unpivoting selected attribute columns and unpivoting all columns except anchors.

This lecture demonstrates how new columns added to source data can cause unpivot transformations to break. You’ll learn why this happens and why it matters.

In this lecture, we show how to consolidate data from multiple sheets within a workbook using Power Query’s UI.

This lecture explores what can go wrong when consolidating multiple sheets using UI-based approaches, especially when sheet structures are inconsistent.

In this lecture, we introduce the important concept of silent versus blocking errors. You’ll learn why silent errors are more dangerous and how they relate to fragile query design.

In this final lecture of Module 3, we summarize key lessons learned and explain how these pitfalls lead directly into learning M-code and more robust design patterns in the next modules.

In this lecture, we introduce Module 4 and explain why understanding data types is essential when working with M-code. We’ll also set expectations for how this module fits into the overall course.

This lecture explains why data types are important when working with M Language.

Using a simple coffee analogy, you’ll see why it’s critical to know what you’re creating before applying logic or functions — and how this mindset applies directly to working with data in M.

In this lecture, you’ll learn about the Text data type in the M language.

We’ll cover what text values are, how to create them, and explore common text functions that are frequently used when cleaning and transforming data.

This lecture focuses on the Number data type in M.

You’ll learn how numeric values are created, how they behave, and review common number-related functions used in calculations and transformations.

In this lecture, you’ll learn about the Date data type in the M language.

We’ll discuss how dates are represented, how to create date values, and review commonly used date functions that are essential for time-based analysis.

This lecture introduces Lists, one of the most important data structures in M.

You’ll learn what lists are, how to create them, and explore common list functions that are used behind the scenes in many Power Query operations.

In this lecture, we cover Records in the M language.

You’ll learn how records store structured information, how to create them, and how they are commonly used within Power Query transformations.

This lecture focuses on Tables, the most familiar data type for Excel and Power BI users.

You’ll learn how tables are represented in M, how to create table values, and review common table functions used in Power Query.

In this final lecture of Module 4, we summarize the key data types and concepts you’ve learned.

We’ll also explain how this foundation prepares you for the next module, where you’ll start working with expressions, logic, and more intentional M-code design.

In this lecture, we introduce Module 5 and its content. We’ll also discuss how this module prepares you for designing robust solutions.

This lecture explains why learning expressions and logic is essential when working with M.

Using a language analogy, you’ll see how data types form the vocabulary, while expressions and structure provide the grammar needed to communicate intent clearly.

In this lecture, you’ll learn what an expression is in the M language.

We’ll explain how expressions combine values, functions, and formulas to produce results, forming the foundation of all M-code.

This lecture focuses on how expressions are stored in steps and executed sequentially.

You’ll learn how Power Query evaluates steps, and why the order of steps matters when designing transformations.

In this lecture, we break down the let–in structure, which wraps expressions and steps in M.

You’ll learn how this structure organizes logic and controls how values flow through a query.

This lecture introduces custom functions in M language.

You’ll learn what custom functions are, when they are useful, and how they help reduce repetition and improve maintainability.

In this lecture, we focus on the syntax of custom functions.

We’ll walk through how functions are defined, how inputs are specified, and how outputs are returned in M

This lecture demonstrates a simple custom function that takes a numeric input and applies transformations to it.

The goal is to make function concepts concrete and easy to follow.

In this lecture, you’ll build a custom function that performs transformations on a table.

This is an important step toward applying functions to real-world data problems.

This lecture explores different ways to call a custom function.

You’ll learn how to call a function from a blank query and how to embed it directly into existing M-code.

In this lecture, you’ll learn how to call a custom function by creating a new column in a table.

This method is commonly used when applying logic row by row.

This lecture introduces parameters.

You’ll learn what parameters are, when to use them, and how they help make queries more flexible and reusable.

In this lecture, we introduce Try…Otherwise as a defensive programming technique.

You’ll learn how to handle unexpected values and prevent queries from failing when data changes.

In this final lecture of Module 5, we summarize the key concepts you’ve learned and explain how they prepare you for the final module, where everything is applied to real-world scenarios.

In this lecture, we introduce Module 6 and explain how this module brings together everything you’ve learned so far. We’ll focus on designing scalable, predictable Power Query solutions using mental models and guardrails.

This lecture revisits common unpivot pitfalls discussed earlier in the course. We’ll quickly recap why these issues occur and why they matter when designing robust transformations.

In this lecture, we walk through the final desired outcome of the solution. You’ll see what the end result should look like before we begin designing and implementing it.

This lecture focuses on designing the mental model for the solution. We’ll break down the problem, identify risks, and define the logic that will guide our implementation.

In this lecture, we implement the mental model using AI-assisted M-code. You’ll see how to translate clear, structured logic into code with AI acting as a helper, not a replacement for thinking.

This lecture focuses on testing the solution. You’ll learn how to validate behavior, check edge cases, and confirm that the setup works as intended when data changes.

In this lecture, we design a mental model and guardrails for consolidating data from multiple sheets. We’ll identify common risks and plan how to handle them upfront.

This lecture shows how to implement the multi-sheet consolidation model using AI-assisted M-code, applying the same principles of clarity, structure, and robustness.

In this lecture, we test the multi-sheet consolidation setup to ensure it behaves correctly when sheet structures or data change.

This lecture covers how to handle Changed Type steps safely by parameterizing column names, making your queries more resilient to schema changes.

In this lecture, you’ll learn how to handle row-level errors that can occur after type changes, and how to branch or isolate problematic records without breaking the entire query.

This lecture applies the same mental model and guardrail approach to folder-based consolidation, showing how scalable thinking transfers across scenarios.

In this final lecture, we wrap up Module 6 and the course as a whole.