1. Multi-run CAE data challenges

A single CAE study rarely produces a single result. A typical design-of-experiments campaign, a parametric sweep, or a load-case matrix generates dozens to thousands of solver runs, each writing its own output directory. Before any post-processing, comparison, or reporting can begin, those scattered artifacts must be turned into one coherent, queryable table. This chapter establishes the data landscape you will be automating: what solvers actually write, how those files are organized on disk, and why Power Query is a strong fit for consolidating them.

What a "run" actually leaves behind

In multi-run CAE work, a run is one solver execution bound to one combination of input parameters — a mesh density, a material variant, a load magnitude, a turbulence model. Each run typically produces three distinct classes of output, and they behave very differently when you try to consolidate them:

• Scalar summary results — maximum stress, total mass, drag coefficient, first natural frequency, peak temperature. These are small, one value per quantity per run, and are the natural target for a consolidated table.
• Tabular time-history or frequency data — force-displacement curves, transient probe signals, frequency response functions. These are long and narrow, often one row per time or frequency step.
• Binary or proprietary result databases — .odb, .rst, .op2, .h3d, .unv. These cannot be read directly by Power Query and must be exported to text or CSV first, usually via solver scripting or a post-processor batch job.

The practical consequence: Power Query consolidates the exported layer, not the binary layer. A reliable pipeline therefore has an upstream export step (solver script, Python post-processing, or batch macro) that writes deterministic, well-named text files, and a downstream Power Query step that ingests them. Designing both together is what makes the automation robust.

File structures that break naive consolidation

The most common failure mode in multi-run consolidation is assuming every run directory looks the same. In practice, three structural variations appear repeatedly:

Consider a representative layout for a structural sweep:

study_root/
├── run_001_mesh2mm_load800/
│   ├── summary.csv          # scalar KPIs
│   ├── history_probe_A.csv  # time history
│   └── solver.log
├── run_002_mesh2mm_load1200/
│   ├── summary.csv
│   ├── history_probe_A.csv
│   └── solver.log
└── run_003_mesh1mm_load800/
    ├── summary.csv
    ├── history_probe_A.csv
    └── solver.log

Here the run identity is split across the folder name (mesh, load) and the folder index (run number). A correct consolidation must extract all three, attach them to every row of every file, and only then append. If you simply point Power Query at summary.csv and combine, you get rows but no run context — the table is useless for comparison.

Why manual consolidation fails at scale

Copy-pasting results into a master spreadsheet works for five runs and collapses at fifty. The failure is not just effort; it is silent corruption. Manual consolidation introduces row misalignment when a run is added or removed, unit drift when a solver setting changes, and stale values when a run is re-executed but the spreadsheet is not refreshed. In a DOE with 200 runs and 12 KPIs, a single misaligned row invalidates every downstream sensitivity plot. Automation is not a convenience here — it is the only way to guarantee that the consolidated table is a faithful, reproducible projection of the run directory.

Where Power Query fits

Power Query is well suited to this problem because its core abstraction — a folder query that enumerates files, extracts content, and expands columns — maps directly onto the run-directory structure. The key capabilities that matter for CAE consolidation are:

• Folder connector with Folder.Files, which returns one row per file with Folder Path, Name, and Content columns.
• Path parsing via Text.BetweenDelimiters or Text.Split to lift parameters out of folder names.
• Schema normalization via Table.TransformColumnNames and explicit type assignment, so heterogeneous headers converge.
• Incremental refresh so that adding a new run folder only reprocesses new files, not the entire study.

The diagram below shows the end-to-end data flow from solver output to a consolidated, query-ready table, including the export boundary that Power Query cannot cross on its own.

A minimal worked example

To make the flow concrete, suppose each summary.csv contains a single header row and one data row:

quantity,value,unit
max_stress,182.4,MPa
total_mass,12.1,kg
max_disp,3.42,mm

A folder query over study_root returns one row per file. After filtering to Name = "summary.csv", you add a custom column that extracts the run parameters from [Folder Path]:

let
    path = [Folder Path],
    runId  = Text.BetweenDelimiters(path, "run_", "_"),
    mesh   = Text.BetweenDelimiters(path, "mesh", "_"),
    load   = Text.BetweenDelimiters(path, "load", "/")
in
    [run_id = runId, mesh_mm = mesh, load_N = load]

Expanding that record column, then expanding the parsed CSV content, yields a long table with columns run_id, mesh_mm, load_N, quantity, value, unit. Pivoting quantity gives the wide comparison table shown in the diagram. The important verification step is a row-count check: with 3 runs and 3 quantities, the long table must have exactly 9 rows. If it has 6 or 12, a file was missed or duplicated — a symptom almost always caused by a folder-name pattern that the delimiter logic did not match.

With the data landscape and the consolidation flow established, the remaining chapters build the pipeline piece by piece: robust folder enumeration, parameter extraction, schema normalization, error handling for failed runs, and incremental refresh for studies that grow over time. The foundation laid here — understanding what each run writes and where its identity lives — determines whether every later step is straightforward or fragile.