Learn to connect, clean, and shape data with Power Query in Excel and Power BI.
Learn to build automated data preparation pipelines with Power Query. Connect to multiple sources, apply transformations that handle messy real-world data, and load results efficiently for analysis and reporting.
Master Power Query's core transformation operations with advanced patterns for split, merge, pivot, and unpivot. Learn performance optimization, edge case handling, and enterprise-scale implementation strategies.
Master the essential techniques for combining disparate data sources into unified datasets using Power Query's append and merge operations, with real-world examples and performance optimization strategies.
Master advanced Power Query data cleaning techniques including error propagation systems, sophisticated null handling strategies, and performance-optimized type conversions for enterprise-scale data pipelines.
Learn to create powerful conditional logic and custom columns in Power Query that automatically categorize data, apply business rules, and calculate derived values. Transform raw data into actionable insights with practical, real-world examples.
Master Power Query's text and date functions through practical examples. Learn to parse multiple date formats, clean messy text data, and optimize performance for production workflows.
Master advanced Power Query techniques for building flexible, scalable data solutions that adapt to different environments, schemas, and business requirements through intelligent parameterization.
Build Power Query ETL solutions that your future self will thank you for. Learn modular design, parameter management, error handling, and testing strategies that create robust, scalable data pipelines.
Master advanced techniques for building robust, automated pipelines that extract data from any web source, handle authentication complexities, and maintain themselves even when APIs evolve.
Learn how query folding works, when Power Query buffers data, and optimization techniques that transform slow refreshes into lightning-fast data pipelines. Essential skills for building scalable Power Query solutions.
Full refreshes at scale are a performance disaster — but Power Query gives you the tools to build watermark-based incremental pipelines that only process what's actually changed. This lesson walks through the complete architecture: persistent state, upsert merging, late-arrival handling, and failure recovery.
Most Power Query users copy-paste transformations across reports and spend hours on maintenance. This deep-dive lesson shows you how to design, build, and deploy genuine M code function libraries — with real recursion, parameterization, and modular composition patterns that experienced data engineers actually use.
Every click you make in Power Query silently writes M code behind the scenes. This lesson demystifies M's syntax, walks you through its data types, and gives you the foundation to read, write, and modify M expressions with confidence — no programming background required.
Schema drift silently breaks Power Query pipelines when source systems add, remove, or rename columns. This lesson teaches you to design transformations that adapt to changing data structures without failing — using dynamic column detection, mapping tables, and conditional logic in M.
Most Power Query developers treat error handling as an afterthought — a quick `try-otherwise null` and move on. This lesson teaches you the full depth of M's error model: row-level isolation, tiered handling strategies, diagnostic workflow design, and the performance traps that will bite you in production.
Raw transaction data is only useful when you can summarize it. This lesson teaches you how to use Power Query's Group By feature to collapse thousands of rows into meaningful summaries — by region, by product, by sales rep, or any combination you need. Walk away with real, practical aggregation skills you can apply immediately.
Most real-world APIs and data files are nested — JSON objects inside arrays inside objects, XML elements three levels deep. This lesson teaches you how to systematically expand Lists, Records, and Tables in Power Query, handle multi-level nesting, and write M code that doesn't break when the source schema changes.
Learn to build production-grade Power Query custom connectors from scratch using the M SDK. This deep-dive lesson covers OAuth 2.0 authentication, navigation tables, pagination, schema enforcement, and distribution — everything a real connector needs.
Messy data rarely arrives in the shape you need it. This lesson teaches you how to flip, fill, and restructure tables in Power Query using Transpose, Fill Down, and Fill Up — the three tools that turn awkward exports into clean, analysis-ready datasets.
Many-to-many joins are the most common source of silent data corruption in Power Query — and they produce no error messages. Learn how to detect M:N relationships before they inflate your totals, and master three strategies to handle them correctly: deduplication, bridge tables, and defensive merge patterns.
Row-Level Security enforcement lives in Power BI, but the data structures that make it work — or break it — are built in Power Query. This expert-level lesson teaches you how to design user mapping tables, security bridge tables, and hierarchical access models in M that are robust, performant, and production-ready.
Power Query doesn't run your steps in the order you wrote them — it uses a dependency-driven evaluation model that surprises almost every new user. Learn how the M engine actually decides what to compute, when, and why, so you can write faster and more reliable queries.
Stop maintaining static date CSVs that break at year-end. This deep-dive lesson shows you how to build a fully dynamic, self-updating date dimension table in Power Query M code — complete with fiscal year logic, ISO week numbers, and relative date flags that update automatically on every refresh.
Most analytics models quietly corrupt historical data by overwriting dimension attributes without any tracking strategy. This deep-dive lesson teaches you to implement both Type 1 (overwrite) and Type 2 (version) SCD merge patterns entirely in Power Query M, with complete code, realistic scenarios, and honest guidance on when the tool reaches its limits.
Learn how to filter and sort rows in Power Query with genuine control — not just clicking dropdowns, but understanding the M code behind each step. This lesson uses a realistic sales dataset to teach text filters, date ranges, number conditions, multi-column sorting, and troubleshooting the mistakes that trip up beginners.
Learn how to build production-ready cross-tabulated reports entirely in Power Query. This lesson goes beyond the Pivot Column dialog to teach you how to pre-aggregate data, add dynamic totals rows and columns, enforce column order, and handle the edge cases that break most pivot implementations.
Most Power Query practitioners either don't know fuzzy matching exists or apply it naively and produce garbage results. This deep-dive lesson covers how the n-gram Jaccard engine actually works, how to build multi-field probabilistic scoring with blocking for scalability, and how to validate your results with precision and recall — so you can trust what comes out.
Learn how to build reusable, refreshable data connections in Power Query — including how to connect to external Excel workbooks, named tables, and named ranges. This foundation lesson turns a two-hour monthly chore into a one-click refresh.
Legacy flat files from mainframes, ERPs, and AS/400 systems don't cooperate with Power Query's default import wizard. This lesson teaches you how to parse fixed-width and non-standard delimited formats with full control — from raw binary loading to reusable, dynamic parsers.
Power Query's formula firewall and privacy level system are widely misunderstood, and misconfiguring them leads to either broken pipelines or silent data leaks. This deep-dive lesson explains exactly how the firewall works, how to design multi-trust-level ETL architectures correctly, and how to manage credentials securely across enterprise deployments.
Stop importing entire database tables and wondering why your reports are slow. This lesson teaches you how to connect Power Query directly to SQL Server, write native SQL queries that filter data at the source, and manage credentials the right way — skills that immediately make your reports faster and more professional.
Refresh failures are inevitable in production Power BI environments — but silent failures are a choice. Learn how to build layered alerting, read diagnostic error messages accurately, trace failures to specific M query behavior, and execute a structured recovery workflow that keeps you in control.
Single-query Power Query pipelines collapse under real-world complexity. This deep-dive lesson teaches you how to design and implement a three-layer staging architecture — Raw, Cleansed, and Conformed — that separates concerns, enforces business rules in the right place, and scales as your data estate grows. Walk away with complete M code patterns, performance optimization strategies, and a hands-on exercise you can apply immediately.
Raw data exports are rarely structured the way you need them. Learn how to rename cryptic column names, drop unnecessary fields, and arrange columns in a logical order using Power Query — and understand the M code doing the work behind the scenes.
When reference data changes — customer segments, sales territories, product categories — a simple merge applies today's values to all your historical records. This lesson shows you exactly how to build a slowly changing lookup table with effective date ranges and merge it correctly in Power Query for accurate point-in-time reporting.
Late-arriving records silently corrupt historical data in most Power Query pipelines — and most tutorials never mention they exist. This deep-dive lesson gives you a complete architectural framework for detecting, classifying, and reconciling backdated facts using watermarks, dual-date tracking, SCD2 temporal joins, and audit log patterns.
SharePoint is one of the most common data stores in the Microsoft ecosystem — and one of the trickiest to connect to cleanly. This lesson walks you through authenticating, constructing the right site URL, and extracting usable data from both SharePoint Lists and Document Libraries in Power Query.
When Power Query evaluates a complex pipeline, it decides what to refresh, when, and how many times — unless you architect it otherwise. This lesson teaches you how to take control of evaluation order, buffer volatile sources like APIs and SharePoint lists, and build pipelines that are deterministic and reliable in production.
Building a Power Query pipeline that serves multiple tenants from shared infrastructure requires more than adding a filter — it demands layered isolation guarantees, credential management discipline, and query patterns that fail safely when configuration goes wrong. This lesson teaches you the complete architecture for building multi-tenant ETL pipelines you can trust with sensitive data.
Most REST APIs return data in pages, not all at once — and if you don't know how to follow the pagination, you're only seeing a fraction of your data. This lesson teaches you how to write M code that loops through every page of an API response, handles cursor tokens and offset parameters, and respects rate limits so your queries don't get blocked.
Learn how to build assertion-style validation pipelines in Power Query that automatically catch nulls, duplicate keys, and referential integrity failures before data reaches your model. This lesson gives you a complete, reusable quality gate architecture with copy-paste-ready M code for real production pipelines.
Surrogate key generation is one of the most failure-prone steps in data warehouse ETL — especially when sources disagree on key formats. This deep-dive lesson teaches you deterministic hashing, offset-aware sequence indexing, and a robust cross-source mapping table pattern in pure M code.
M — Power Query's formula language — represents all data using three container types: Tables, Lists, and Records. Learn how each one works, how to navigate inside them with M syntax, and how they nest together to form the complex structures you see in JSON and API data.
Learn how to build a reusable M code framework that automatically profiles every column in your Power Query pipeline — computing completeness rates, statistical distributions, IQR and Z-score outlier flags, and pass/fail quality gates on every refresh. Stop discovering data problems after the model is built.
Full-load refreshes that process millions of rows to capture hundreds of changes are wasteful and slow. Learn how to implement production-grade watermark-based Change Data Capture in Power Query — complete with persistent high-water mark storage, query-folded delta fetches, multi-table orchestration, and recovery logic for real-world edge cases.