The Ingestion Engine: Power Query M, Query Folding & Source Pushdown

Power Query is the data extraction and transformation gateway of the Microsoft Power Platform. While end-users interact with its graphical interface, every transformation compiles into M—a purely functional, case-sensitive, order-independent formula language. In high-scale enterprise reporting, understanding how the M evaluation engine delegates transformations to source systems is the boundary between sub-second dashboards and fatal gateway timeouts.

1. The Power Query Evaluation Container & Storage Modes

When connecting Power BI to external databases, you must determine how data is physically held and queried:

┌────────────────────────────────────────────────────────────────────────────────────────┐
│ POWER BI STORAGE MODES COMPARED                                                        │
├───────────────────┬────────────────────────────────────────────────────────────────────┤
│ Storage Mode      │ Physical Architecture & Engine Execution                           │
├───────────────────┼────────────────────────────────────────────────────────────────────┤
│ Import Mode       │ Ingests, structures, and compresses data into the in-memory        │
│                   │ VertiPaq columnar database. Maximum DAX performance.               │
├───────────────────┼────────────────────────────────────────────────────────────────────┤
│ DirectQuery Mode  │ Zero data cached locally. Visual interactions generate dynamic     │
│                   │ T-SQL/native queries executed directly on the source database.    │
├───────────────────┼────────────────────────────────────────────────────────────────────┤
│ Dual Mode         │ Hybrid mode for dimensions in composite models. Acts as VertiPaq   │
│                   │ cache for Import visuals and folds locally for DirectQuery joins.  │
├───────────────────┼────────────────────────────────────────────────────────────────────┤
│ Direct Lake Mode  │ Modern Fabric standard. Directly reads Delta Parquet tables on     │
│ (Microsoft Fabric)│ OneLake into VertiPaq RAM without ETL refresh or SQL translation. │
└───────────────────┴────────────────────────────────────────────────────────────────────┘

2. VertiPaq's 3-Stage Columnar Compression Pipeline

The VertiPaq in-memory engine achieves dramatic memory reductions (typically 10x to 20x) through three sequential encoding passes:

Value Encoding: Applied to narrow-range integer columns. VertiPaq calculates the mathematical offset from the column minimum (EncodedValue = ActualValue - MinValue). This minimizes the bit-width required per row and allows the Formula Engine to perform mathematical aggregations directly on encoded values without dictionary lookups.
Hash / Dictionary Encoding: Applied to text strings, dates, and non-linear numbers. VertiPaq builds a distinct lookup dictionary and replaces raw values with zero-based bit-packed integer IDs. Dictionary size is determined by column cardinality (the count of unique values), which is why high-cardinality strings (GUIDs, timestamps) bloat model size.
Run-Length Encoding (RLE): After dictionary encoding, VertiPaq scans down the physical column and replaces contiguous identical values with (ValueID, RunCount) pairs. Table sort order directly impacts RLE efficiency: sorting a table by low-cardinality dimensions before higher-cardinality ones clusters identical values, reducing RAM consumption by up to 90%.

3. The Clean Ingestion Playbook & Roche's Maxim

Roche's Maxim of Data Transformation: "Data should be transformed as far upstream as possible, and as far downstream as necessary."

Enterprise BI architects enforce three foundational ingestion principles:

A. Upstream Pushdown vs. Calculated Column Waste

Never use DAX calculated columns to clean data, parse text strings, or concatenate keys. Calculated columns are computed during data refresh and stored uncompressed in VertiPaq memory without the benefit of run-length sorting optimization. Perform all cleaning inside SQL views or Power Query steps.

B. The Canonical Unpivot Pattern

Operational spreadsheets and legacy reporting systems frequently export cross-tabulated matrices where time intervals (e.g. Month columns: Jan, Feb, Mar) stretch horizontally across the screen. Tabular engines cannot create relationships across dynamic columns. Developers must select attribute columns (e.g. AccountID, Department) and execute Table.UnpivotOtherColumns to normalize into relational [Attribute, Value] rows before loading into the model.

C. Strict Type Coercion & Schema Hygiene

Leaving column data types as Any or storing integer foreign keys as decimal floats increases dictionary overhead and disables Value Encoding. Always explicitly cast types in the initial ingestion step.

4. Query Folding Mechanics & Breaking Steps

Query Folding is the ability of the Power Query Mashup Engine to compile M transformation steps into a single native SQL query (e.g. SELECT ... WHERE ... GROUP BY), delegating execution to the source relational server.

┌───────────────────────────┐
│     POWER QUERY GUI       │  Filter Rows, Change Type, Remove Columns
└─────────────┬─────────────┘
              │ Compiles to M
              ▼
┌───────────────────────────┐
│   M MASHUP ENGINE         │
└───────┬───────────┬───────┘
        │           │
[Foldable Steps]    [Non-Foldable Step Breaks Chain]
        │           │
        ▼           ▼
┌───────────────┐ ┌────────────────────────────────────────────────────────┐
│ NATIVE SQL    │ │ LOCAL CLIENT / GATEWAY RAM FALLBACK                    │
│ PUSHDOWN      │ │ Pulls millions of un-filtered rows over the network;   │
│ WHERE, JOIN,  │ │ performs sorting/filtering locally in memory;          │
│ GROUP BY      │ │ Triggers OOM errors and scheduled refresh timeouts.    │
└───────────────┘ └────────────────────────────────────────────────────────┘

Steps That Fold:

Filtering rows (Table.SelectRows $ ightarrow$ SQL WHERE)
Selecting or removing columns (Table.SelectColumns $ ightarrow$ SQL SELECT)
Joining tables (Table.NestedJoin $ ightarrow$ SQL INNER / LEFT JOIN)
Grouping aggregations (Table.Group $ ightarrow$ SQL GROUP BY)

Steps That Break the Chain:

Adding an Index Column (Table.AddIndexColumn)
Invoking non-foldable custom M functions or external APIs (Text.PositionOf)
Caching tables in client memory via Table.Buffer()
Changing column types to data types unsupported by the source database

Overriding Folding Breaks with `Value.NativeQuery`:

When connecting with custom SQL, downstream UI steps fail to fold by default. Enable folding explicitly in the M script:

let
    Source = Sql.Database("db-server.corp.net", "EnterpriseDW"),
    CustomQuery = Value.NativeQuery(
        Source, 
        "SELECT OrderID, CustomerID, OrderDate, Amount FROM dbo.Orders WHERE Status = 'Completed'", 
        null, 
        [EnableFolding = true]
    ),
    FilteredRows = Table.SelectRows(CustomQuery, each [Amount] > 100)
in
    FilteredRows