A Critical Re-examination of the Power Query Execution Model (Revised Edition — Critical Verification Chapter Added)
- Author: An Idle Employee at a Non-IT Japanese Corporation
- X (Twitter): @kalppi1111
Abstract
This is a personal conceptual model, not an official description of Power Query's internal implementation.
This paper presents an execution model that describes the simultaneous handling of "relational data (Table)" and "schema-less nesting (List / Record)" in the Microsoft Power Query runtime (the M language) through the kinematics of a three-dimensional surface called the Oloid, discovered by Paul Schatz in 1929. The Oloid is the convex hull of two congruent circles placed in mutually orthogonal planes such that the center of each circle lies on the circumference of the other; it is a developable surface whose entire surface touches the ground as it rolls[cite: 1].
This paper uses the shape as a metaphor for the one-directional evaluation sustained by the immutability and lazy evaluation of the M language[cite: 1]. However, the prior naive formulation has two counterexamples. First, non-constant height: the center of gravity of the standard Oloid oscillates vertically, exhibiting two minima and two maxima per rolling period[cite: 1]. Second, non-orthogonality: a Table is a column of Records, and a Record's value can itself be a Table — a recursive inclusion[cite: 1].
Chapter 5 of this paper verifies these two counterexamples head-on and revises the model's claim from "constant-height Oloid" to "standard Oloid preserving its sway", and from "orthogonal" to "linking"[cite: 1]. In conclusion, the Oloid model is valid not as a proof but as a constrained execution model, and its scope is bounded by two dynamical regimes: the "ideal path" where query folding is preserved, and the "real path" where folding breaks[cite: 1].
1. Introduction
For a long time, enterprise data platforms have been discussed in terms of a dichotomy: relational databases (SQL) versus schema-less systems (NoSQL). Yet what practitioners face in the field is a situation where well-structured tables and nested, unstructured data coexist within the very same cells. The purpose of this paper is to take Power Query as a subject and to describe this coexistence as a single kinematic model[cite: 1].
Let me state in advance: the geometry presented here is no mere decorative metaphor. Nor, however, is it a proof. As shown below, the model is clearly bounded by two counterexamples; in that sense its "validity as an execution model" is conditional. To state this condition explicitly is the central contribution of this paper[cite: 1].
Note (origin): The perspective of this paper came from reading Bartosz Milewski's Category Theory for Programmers. By applying his idea that "objects should be understood through relations and structure rather than through their contents" to the Power Query execution model, the discussion here takes its starting point[cite: 1].
2. Background: The Execution Semantics of the M Language
Beneath Power Query's surface-level GUI lies a functional language called M. M is a case-sensitive, functional language similar to F#[cite: 1]. Its specification is described in terms of values, expressions, environments, variables, and an evaluation model[cite: 1].
2.1 The Duality of Environment and Expression
According to the M specification, an expression is evaluated in a given environment, where the environment is a collection of named values (variables)[cite: 1]. That is, the bearer of meaning is not the expression alone but the pair of environment and expression. In this paper, this duality is called the circle on the left (the execution-engine side)[cite: 1].
2.2 Immutability and Lazy Evaluation
An M script is not a procedural sequence of instructions but a large, immutable expression tree. Data flows in one direction only, and there is no rolling back of state. This irreversible baseline is what this paper calls immutable ground. Evaluation is delayed until demanded, and this deferral opens up the room for "pushing evaluation back toward the source"[cite: 1].
Note (unverified item): The design of the M language is sometimes attributed to Erik Meijer, but the investigation in this paper could not confirm a direct attribution from a primary source. Accordingly, the reference to Meijer is kept at the level of "an influential lineage" and is not used as a pillar of the argument here[cite: 1].
3. Structural Model: The Duality of Tables and Lists
3.1 Two Vectors of the Data Structure
In M's value model, a Table is treated as a list of Records[cite: 1]. A Record is also a list of fields, and the value of each field can itself be a Table, List, or Record[cite: 1]. This paper treats the two as two navigational axes: the horizontal lattice-like access (Table = relational vector) and the recursive vertical access (List = nested-structural vector)[cite: 1].
3.2 Correspondence with the Oloid Structure
The Oloid is the convex hull of two congruent circles of equal radius, placed in planes that are orthogonal to each other, with the center of each circle lying on the circumference of the other[cite: 1]. Its principal properties are summarized by the following three points:
- Developability: its surface is composed of straight ruling lines (a developable surface / torse), and it is one of the very few shapes whose entire surface touches the ground as it rolls[cite: 1].
- Center-to-center distance equals the radius: in the standard Oloid, the distance between the two circle centers equals the radius[cite: 1].
- Non-occlusion: as it rolls, it neither occludes nor erases the opposing face[cite: 1].
This paper maps (1) to the property of "transformation being flattened without loss", and (3) to the fact that "both paradigms can be observed simultaneously". In Chapter 4, (2) is used as the grounding condition of the dynamics[cite: 1].
Figure 1. Kinematic mapping in the M language evaluation model (pure functional axis and dual environment)
4. Dynamical Model: Grounding Conditions
In this model, the rolling direction of the Oloid corresponds to the step sequence of Power Query. That is, the program flow from Source → Filter → Expand → Merge → Output is itself the rolling direction. The Oloid does not shuttle back and forth between the relational axis and the nested-structural axis; it carries both within itself while moving in the direction of evaluation[cite: 1].
What, then, is the "ground" the Oloid is in constant contact with? It is none other than M's evaluation context and the execution substrate for query folding. Power Query's GUI presents processing as a procedural sequence of operations, but its true substance is a chain of function applications in M[cite: 1]. Each step is not an instruction that rewrites state but a mapping that derives a new value from the previous one. In this sense, the rolling of the Oloid is not a movement of data but a continuation of transformations, and rolling on means that evaluation continues without interruption and remains folded into the engine side[cite: 1].
As a concrete example, consider a single execution of the ExpandTableColumn step. Just before the step, the column in question resides in a nesting whose elements are Records or Tables — a nested-structural edge. Once the step executes, the column is expanded and becomes a flat row-by-column relational form — a relational edge[cite: 1]. During this, the source data itself is not rewritten; only a new step is appended to the M expression tree[cite: 1].
5. Critical Verification: Two Counterexamples
This chapter is the core of the paper. The prior naive formulation (the first edition) is exposed to counterexamples on two points, and rather than defending the model, the claims are revised[cite: 1].
5.1 Counterexample A: Non-constant Height (Sway)
The first edition claimed that "once the proportions are optimized the center of gravity becomes perfectly level and the contact trace flattens into a pure sine wave." This, however, is geometrically inaccurate[cite: 1].
For the standard Oloid ($d = r$), the center of gravity executes a meandering motion with two minima and two maxima in the contact distance per rolling period[cite: 1]. It becomes level at a constant height only when $d = \sqrt{2}r \approx 1.4r$ (the extended Oloid)[cite: 1].
- Standard Oloid ($r$): Represents the realistic refresh path. The sway corresponds to steps occurring with evaluation delays or schema drifts (e.g., "column not found" errors)[cite: 1].
- Extended Oloid ($r\sqrt{2}$): Represents the ideal path where query folding is preserved[cite: 1].
5.2 Counterexample B: Non-orthogonality (Inclusion)
Calling Table and List "orthogonal vectors" is incorrect in the linear-algebraic sense because of recursive inclusion (Tables holding Records, which hold Tables/Lists)[cite: 1].
- Revision: Replace "orthogonal" with "linking" (mutual constraint without mutual penetration, perfectly matching the topological definition of the two generating circles in an Oloid)[cite: 1].
5.3 Counterexample C: Discontinuity of Developability
Developability (lossless flattening) holds only over the interval where query folding is preserved. Beyond steps that break native query translation, developability is lost[cite: 1]. Furthermore, the "sine wave" contact trace is demoted to an in-model idealization rather than an observed physical certainty[cite: 1].
5.4 Summary of Revisions
| First-Edition Claim | Counterexample | Revised Claim | Basis |
|---|---|---|---|
| Center of gravity is constant height | Standard Oloid is not constant height | Standard Oloid = realistic path (sway); Extended ($r\sqrt{2}$) = ideal path | [cite: 1] |
| Table and List are orthogonal | Recursive inclusion exists | Replace "orthogonal" with "linking" | [cite: 1] |
| Developable surface = lossless flattening | Folding can break | Bounded to the interval where folding is preserved | [cite: 1] |
| Contact trace is a pure sine wave | Not verifiable | Demoted to an in-model idealization | [cite: 1] |
6. Conclusion: The Oloid on the Desk
While data designers argue over the "right foundation", countless office workers are rolling Oloids inside Excel cells, chasing deadlines and pressing buttons[cite: 1].
The contribution of this paper is not to describe this daily work in geometric terms, but to make explicit where that description breaks. Yet, on reading sway as a real evaluation step, linking as inclusion, and developability as the folding interval, the model survives[cite: 1]. As long as folding is preserved, M's functional axis slides along the immutable ground while embracing its two linked data axes[cite: 1]. The sway is not an error; it is evidence that the model reflects reality[cite: 1].
References
- Oloid — Wikipedia[cite: 1]
- Oloid — Wolfram MathWorld[cite: 1]
- Oloid — Wikiwand[cite: 1]
- HITS, The Oloid[cite: 1]
- Dirnböck, H. & Stachel, H., "The Development of the Oloid", Journal for Geometry and Graphics[cite: 1]
- MathOverflow: Oloid and sphericon rolling[cite: 1]
- arXiv: Convex Hull of Two Circles in R^3[cite: 1]
- Power Query M formula language reference — Microsoft Learn[cite: 1]
- Power Query M language specification — Microsoft Learn[cite: 1]
- M Language basic concepts — Microsoft Learn[cite: 1]
- Table.ToRecords — PowerQuery.how[cite: 1]
- Table.FromRecords — Microsoft Learn[cite: 1]
- Work with a List, Record, or Table structured column — Microsoft Support[cite: 1]
- Handling Dynamic Schema Changes in Power Query — Wicked Smart Data[cite: 1]
- Query folding guidance in Power BI Desktop — Microsoft Learn[cite: 1]
- Understanding Query Folding in Power BI — CertLibrary[cite: 1]
- Power Query editor has changed type errors with new source — Microsoft Fabric Community[cite: 1]
Top comments (1)
hi