2. Power Query fundamentals for CAE engineers

CAE post-processing rarely fails because the solver is wrong. It fails because the same extraction, renaming, unit conversion, and pivot logic gets rebuilt by hand for every run, every solver version, and every test article. Power Query is the layer that turns that repeated manual work into a repeatable, auditable transformation pipeline. For a CAE engineer, the mental model to adopt is this: Power Query is a functional, lazily evaluated data-preparation engine, and the M language is the small expression language you use to describe each transformation step.

This chapter establishes the three foundations you need before automating multi-run CAE data: how M evaluates expressions, how query folding decides whether work happens in the source or in the engine, and how to operate the Power Query Editor efficiently when your inputs are CSV exports, Excel workbooks, and folder-based run directories.

M Language: Expressions, Not Statements

M has no statements, no loops in the imperative sense, and no mutable variables. Everything is an expression that evaluates to a value. A query is a single let expression: a sequence of named steps, each referring to earlier steps, terminated by an in clause that returns the final value.

let
    Source = Csv.Document(
        File.Contents("D:\CAE\Run_042\displacement.csv"),
        [Delimiter = ",", Columns = 4, Encoding = 65001]
    ),
    Promoted = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(Promoted, {
        {"NodeID", Int64.Type},
        {"Time_s", type number},
        {"Ux_mm", type number},
        {"Uy_mm", type number}
    }),
    Filtered = Table.SelectRows(Typed, each [Time_s] >= 0.5)
in
    Filtered

Three properties of this snippet matter more than the syntax. First, steps are names bound to values, not sequential mutations — Filtered does not modify Typed; it is a new table derived from it. Second, evaluation is lazy: nothing executes until a consumer (the load destination, or the preview pane) requests a value, which is why the Editor can show a preview of step 3 without materializing step 7. Third, the each keyword introduces an implicit row context, so each [Time_s] >= 0.5 is shorthand for a one-argument function applied per row.

For CAE work, the most valuable M functions are the table shapers and the folder combiners. Table.TransformColumns handles unit conversion; Table.Group computes per-run statistics such as peak displacement or RMS acceleration; Table.Pivot and Table.UnpivotOtherColumns restructure wide solver output into tidy long form; and Folder.Files plus Table.AddColumn with Csv.Document turns a directory of run folders into a single appended table. A compact per-run aggregation looks like this:

Peaks = Table.Group(Typed, {"NodeID"}, {
    {"PeakUx_mm", each List.Max([Ux_mm]), type number},
    {"PeakUy_mm", each List.Max([Uy_mm]), type number},
    {"Samples",   each Table.RowCount(_), Int64.Type}
})

Note the underscore: inside a Table.Group aggregation, _ is the sub-table for the current group, so Table.RowCount(_) counts rows per node rather than across the whole dataset. Getting this wrong is a common source of silently incorrect summary tables.

Query Folding: Where the Work Actually Happens

Query folding is the mechanism by which Power Query translates M steps into native source queries — SQL for a database, OData for a feed, and so on — so that filtering and aggregation execute at the source instead of pulling every row into memory. When folding succeeds, a step is absorbed into the upstream query; when it fails, the engine inserts a barrier and all subsequent steps run locally on the full result set.

Folding is not a binary property of a query; it is a property of each step in sequence. A single non-foldable step — a custom M function, a Table.AddColumn with arbitrary logic, or a type conversion the connector cannot express — truncates the foldable region. Everything before the barrier still folds; everything after it does not. This is why step order is an engineering decision, not a cosmetic one: put filters and column pruning as early as possible, and push expensive row-level logic as late as possible.

CAE data complicates this picture. CSV files, Excel workbooks, and JSON exports generally do not fold at all — the file connector has no query engine to delegate to. Folding becomes relevant when your run metadata lives in a database (a test rig historian, a PLM system, a results database) and your bulk time-history data lives in files. The practical pattern is a split query architecture: a foldable metadata query that selects the run IDs and file paths you need, and a file-based data query that consumes that list. Keep the metadata query foldable so that selecting 40 runs out of 12,000 does not download 12,000 rows.

The Power Query Editor as a Working Surface

The Editor is organized around four regions that you will use constantly: the Queries pane on the left (query groups, dependencies, and query types), the Data preview in the center (a sampled view, not the full dataset), the Applied Steps list on the right (the ordered let steps, each renameable and reorderable), and the formula bar beneath the ribbon, which exposes the M expression behind any selected step.

Two habits separate efficient users from frustrated ones. First, rename steps immediately after creating them — Filtered Rows repeated eleven times is unmaintainable, whereas Filter t >= 0.5 s documents intent. Second, treat the formula bar as the primary editing surface for anything non-trivial: the ribbon generates M, but hand-editing M is faster and more precise for type declarations, try ... otherwise error handling, and custom column logic.

The preview deserves explicit caution. It samples a limited number of rows, so a filter that appears correct in preview may behave differently on the full dataset — particularly with Table.FirstN, sorting, and any step whose result depends on row order. For CAE time histories, always validate a transformation against a known run with a known peak value before applying it across the run set.

A Reproducible First Pipeline

To make the concepts concrete, consider a folder of 40 run directories, each containing displacement.csv with columns NodeID, Time_s, Ux_mm, Uy_mm. The goal is a single table of peak displacement per node per run.

1. Create a blank query and enter Folder.Files("D:\CAE\Runs"). Filter [Extension] = ".csv" and keep only [Folder Path] and [Name].
2. Add a custom column that parses the run ID from the folder path, for example Text.BetweenDelimiters([Folder Path], "Run_", "\"). Verify the output type is text, not a nested record.
3. Add a custom column invoking Csv.Document([Content], [Delimiter=",", Columns=4, Encoding=65001]), then expand the resulting table column. This is the step that prevents folding — it is inherently local, which is expected for file sources.
4. Promote headers, set explicit types on all four numeric columns, and filter [Time_s] >= 0.5.
5. Group by NodeID and RunID, aggregating List.Max([Ux_mm]) and List.Max([Uy_mm]).

Verification is not optional. Pick one run, compute the peak Ux_mm for a specific node by hand or in the solver's own post-processor, and compare it to the grouped output. If the values disagree, the usual causes are: headers promoted after type conversion (producing text-typed numerics that sort lexically), a delimiter mismatch that shifts columns, or a Table.Group aggregation that used [Ux_mm] instead of List.Max([Ux_mm]) and therefore returned a list rather than a scalar. Each of these produces a distinctive symptom — text-typed columns show left-aligned values in the preview, shifted columns show nulls in the last field, and list-valued cells display as List in the preview — so the preview pane is a genuine diagnostic tool when read carefully.

With these three foundations in place — expression-based M, an explicit folding strategy, and disciplined use of the Editor — the multi-run automation in later chapters becomes a matter of parameterizing a pipeline you already trust, rather than debugging a pipeline you cannot inspect.