POWER QUERY M: CUSTOM FUNCTIONS, QUERY FOLDING, API PAGINATION, ERROR HANDLING

Power Query M: Custom Functions, Query Folding, API Pagination, Error Handling is the third volume of the Advanced Excel & Data Modeling series — a complete, production-oriented guide to the language that powers every serious Excel data pipeline. It is written for analysts, engineers, and BI developers who already use Power Query through the UI and want to master the M language underneath it. The book opens by framing what Power Query actually is: a functional, lazy, strongly typed, and foldable transformation engine embedded inside Excel, Power BI, and Fabric. Part I builds the foundations — the editors panes and applied steps, the full M type system (primitive, list, record, table, function), the let-expression model, lazy evaluation, and the list operations that underlie every table transformation. Part II moves into tables: selecting and sorting rows, adding and transforming columns, the six join kinds, grouping and aggregation, pivoting and unpivoting, and locale-aware type conversion with structured error handling. Part III is dedicated to custom functions — defining them, saving them as queries, using optional and typed parameters, writing recursive functions with @, applying higher-order transformations across columns and tables, and building a reusable function library with naming conventions, documentation metadata, and versioning. Part IV tackles the single most important performance concept in Power Query: query folding. It explains how the engine translates M into SQL, which steps fold and which break the chain, how to diagnose folding with View Native Query, why Table.Buffer and Table.AddIndexColumn silently kill performance, and how to build hybrid pipelines that fold first and transform last. Part V is the API layer: Web.Contents and JSON parsing, every authentication pattern from anonymous keys to OAuth 2.0, and a full chapter each on page-number, cursor, and offset pagination — all built with List.Generate. It covers rate limits, exponential backoff, Retry-After, and Microsoft Graph including @odata.nextLink pagination and delta queries. Part VI handles the engineering: parallelism and scheduling, streaming and lazy evaluation at scale, systematic debugging with Query Diagnostics, test queries and property checks, and step-level performance profiling. Part VII closes with ten full case studies — a production REST ETL with cursor pagination and retry, a 50-million-row SQL pipeline that achieves full folding in three seconds, a versioned function library, a legacy ETL migration from stored procedures and VBA, a schema-drift-aware API pipeline, layered Bronze/Silver/Gold dataflows in Fabric, responsible web scraping, a generic pagination library that works against any API, a logging and observability framework, and a forward look at Python in Power Query, Copilot, and Fabric. Every chapter is code-first. M expressions are shown as written, results as they appear, and every concept is paired with a comparison table, benchmark numbers, or a worked example that you can run. By the end you will have a fold-aware mental model of the entire engine, a reusable function library, and a production pattern for every API, SQL, and file source you will ever touch — plus the diagnostic habits to keep a pipeline fast as it grows.