The Library · Insights
Deep dives across data, automation & AI
94 in-depth articles — from Excel foundations to production machine learning. Filter by topic or difficulty to find exactly what you need.
Power QueryExpert
Implementing Custom Culture-Aware Text Classification and Regex Engine Pipelines in Power Query M: Building Reusable Pattern Libraries for Multilingual Data Standardization
Learn to build a full culture-aware text classification and standardization pipeline in Power Query M, complete with a versioned pattern library, a custom regex engine, pre-processing strategies, and normalizers for multilingual data — all packaged as reusable shared queries. This is the lesson that turns ad-hoc text cleanup into a systematic, production-ready system.
26 min read
Power QueryExpert
Implementing Multi-Tenant Data Isolation in Power Query: Parameterized Filtering, Credential Scoping, and Safe Query Patterns for Shared ETL Pipelines
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.
30 min read
Power QueryPractitioner
Implementing Web Scraping and Dynamic HTML Table Extraction Pipelines in Power Query M: Parsing Nested Structures, Handling Anti-Patterns, and Building Resilient Selectors
Go beyond Power Query's default web connector and build production-grade HTML scraping pipelines in M. This lesson covers nested table navigation, merged-cell handling, resilient column selectors, and JavaScript-rendered page extraction — with complete, copy-paste-ready code throughout.
22 min read
Power QueryPractitioner
Orchestrating Multi-Query Refresh Dependencies in Power Query: Controlling Load Order and Isolating Volatile Sources for Reliable Pipeline Execution
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.
21 min read
Power QueryFoundation
Implementing Custom Number Formatting and Currency Conversion Pipelines in Power Query M: Locale-Aware Parsing, Rounding Strategies, and Multi-Currency Normalization
International financial data breaks standard Power Query type conversions in silent, dangerous ways. This lesson teaches you how to build locale-aware number parsers, implement currency-specific rounding rules, and normalize multi-currency transaction tables to a single base currency using a robust, production-ready M pipeline.
17 min read
Power QueryFoundation
Loading Data from SharePoint Lists and Libraries in Power Query: Connection, Authentication, and Practical Data Extraction
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.
19 min read
Power QueryExpert
Cross-Query State Management and Shared Parameter Tables in Power Query M: Centralizing Configuration for Multi-Report Deployments
Managing configuration across dozens of Power BI reports is a maintenance nightmare — unless you centralize it in M. This lesson walks you through building a production-grade shared parameter table system with environment switching, type-safe access, derived configuration, and health checking, all in native Power Query M.
26 min read
Power QueryExpert
Implementing Late-Arriving Facts and Out-of-Order Record Reconciliation in Power Query
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.
26 min read
Power QueryPractitioner
Custom Pivot and Unpivot in Power Query M: Dynamic Column Detection and Multi-Attribute Reshaping
The native Pivot and Unpivot buttons in Power Query break the moment your source schema changes. This lesson teaches you how to write M code that detects columns at runtime, handles multi-attribute unpivot, and builds custom aggregations that go far beyond what the UI can do. Walk away with production-ready patterns you can deploy immediately.
17 min read
Power QueryPractitioner
Implementing Slowly Changing Lookup Tables in Power Query: Point-in-Time Merges with Effective Date Ranges
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.
21 min read
Power QueryFoundation
Combining Queries with Table.NestedJoin, Table.Join, and Merge Strategies in Power Query M: When and How to Use Each
Most Power Query users only ever click Merge and expand the column — but that leaves a lot of power on the table. This lesson teaches you exactly how Table.NestedJoin, Table.Join, and the GUI Merge operation work under the hood, when to use each, and how to avoid the join bugs that silently corrupt your data.
16 min read
Power QueryFoundation
Renaming, Reordering, and Removing Columns in Power Query: Structuring Your Data for Downstream Use
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.
17 min read