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 Table.Buffer and In-Memory Caching Strategies in M to Eliminate Redundant Query Recalculations
Power Query's lazy evaluation engine can silently recalculate the same query steps dozens of times — and most developers never know it's happening. This expert-level lesson teaches you how to diagnose redundant evaluations with Query Diagnostics, apply Table.Buffer with surgical precision, and architect multi-query caching systems that cut refresh times by 50–80%.
25 min read
Power QueryExpert
Implementing Row-Level Security Data Preparation in Power Query: Filtering and Shaping Data for Role-Based Access Models
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.
27 min read
Power QueryPractitioner
Building Multi-Stage ETL Pipelines in Power Query M: Orchestrating Dependent Transformations with Modular Query Chains
Most Power Query pipelines collapse under their own complexity because they mix extraction, transformation, and business logic in the same queries. This lesson teaches you to architect multi-stage ETL pipelines where each query has a single job, dependencies are explicit, and the whole chain is debuggable and maintainable — the way serious production pipelines are built.
20 min read
Power QueryPractitioner
Managing Many-to-Many Relationships in Power Query: Deduplication, Bridge Tables, and Safe Merge Strategies
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.
21 min read
Power QueryFoundation
Text Manipulation and String Functions in Power Query M: Parsing, Splitting, and Transforming Text Data
Raw data is almost always messy — names in all-caps, addresses crammed into one column, phone numbers formatted six different ways. This lesson teaches you to parse, split, extract, and rebuild text data using Power Query's M language functions, with real scenarios and hands-on practice.
15 min read
Power QueryFoundation
Reshaping Data with Transpose, Fill Down, and Fill Up in Power Query
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.
17 min read
Power QueryExpert
Implementing Row-Level Security and Dynamic Data Filtering Pipelines in Power Query M Using Parameter-Driven Permission Tables
Learn how to build a production-grade, parameter-driven row-level security system entirely in Power Query M. This expert lesson covers permission table design, dynamic predicate construction, union vs. intersection filter semantics, query folding implications, and full testability patterns — everything you need to implement real security filtering in your data pipelines.
28 min read
Power QueryExpert
Building Custom Connectors in Power Query: Extending Data Source Support with the M SDK
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.
28 min read
Power QueryPractitioner
Streaming and Pagination Patterns in M: Handling Large APIs and Multi-Page Data Sources with Custom Iterators
Most Power Query tutorials stop at fetching one page of API data. This lesson goes all the way — teaching you how to build robust, production-ready pagination handlers in M that work across offset-based, cursor-based, and link-header APIs using List.Generate and custom iterators. You'll leave with reusable code and a mental model that makes any paginated API manageable.
21 min read
Power QueryPractitioner
Unpacking Nested JSON and XML Structures in Power Query: Expanding Lists, Records, and Tables
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.
22 min read
Power QueryFoundation
Working with Dates, Times, and Duration Values in Power Query M
Date and time calculations break more Power Query projects than almost any other topic. This complete lesson teaches you how M's temporal type system works, how to parse text into proper date types, and how to build real time intelligence patterns from scratch — with hands-on exercises and common error fixes included.
15 min read
Power QueryFoundation
Grouping and Aggregating Data in Power Query: Summarize, Count, and Calculate with Group By
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.
16 min read