The Library · Insights
Deep dives across data, automation & AI
109 in-depth articles — from Excel foundations to production machine learning. Filter by topic or difficulty to find exactly what you need.
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
Power QueryExpert
Implementing Custom Data Connector Authentication and OAuth Flows in Power Query M Language
Most Power Query tutorials stop right before the authentication complexity that actually matters in production. This deep-dive lesson builds a complete OAuth 2.0 Authorization Code flow with PKCE, transparent token refresh, and multi-scheme authentication — everything your security team will require before approving a connector for deployment.
29 min read
Power QueryExpert
Debugging and Error Handling in M: Building Robust try-otherwise Logic and Diagnostic Workflows in Power Query
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.
25 min read
Power QueryPractitioner
Mastering M Language Metadata: Attaching, Reading, and Leveraging Type Annotations for Robust Data Pipelines in Power Query
Most Power Query developers never use M metadata — and their pipelines pay the price in silent failures, brittle type assumptions, and zero self-documentation. This lesson teaches you to attach semantic annotations to tables and columns, build automated validation layers, and generate living schema documentation from your type definitions.
22 min read
Power QueryPractitioner
Handling Dynamic Schema Changes in Power Query: Strategies for Evolving Source Data Structures
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.
19 min read
Power QueryFoundation
Understanding M Language Query Evaluation: Lazy Evaluation, Dependency Graphs, and Step Ordering in Power Query
Most Power Query users think their steps run top to bottom in order — but that's not how the M engine works at all. This lesson breaks down lazy evaluation, dependency graphs, and step ordering so you can finally understand why your queries behave the way they do and how to design them to run faster.
15 min read
Power QueryFoundation
Understanding the M Formula Language: Syntax, Data Types, and Expression Basics
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.
15 min read
Power QueryExpert
Building a Custom M Language Parser and Tokenizer for Dynamic Expression Evaluation in Power Query
Learn how to build a complete expression evaluator in pure M — tokenizer, recursive descent parser, and AST evaluator — that lets Power Query dynamically apply user-defined formulas to table rows at refresh time. This is the deep-dive lesson for when "just use Python" isn't an option and you need to understand how parsers actually work inside a functional language.
26 min read
Power QueryExpert
Building Reusable Power Query Function Libraries: Parameters, Recursion, and Modular M Code Patterns
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.
29 min read
Power QueryPractitioner
Implementing Custom Query Folding Logic in M: Keeping Transformations Native to the Data Source
Query folding can mean the difference between a 2-minute refresh and a 25-minute timeout on large datasets. This lesson teaches you to diagnose folding breaks, restructure queries to preserve them, and use Value.NativeQuery for precise native SQL control — with production-ready patterns throughout.
21 min read