2. Power Query as an ETL tool for CAE

CAE workflows rarely produce a single tidy table. A typical multi-run study generates dozens or hundreds of solver output files, each containing result sets keyed by load case, iteration, or design point. Before any comparison chart or sensitivity study can be built, those scattered artifacts must be extracted, normalized, and consolidated. Power Query — the ETL engine embedded in both Excel and Power BI — is a practical fit for this consolidation layer, provided you understand where its strengths end.

What Power Query Actually Does in a CAE Pipeline

Power Query is a column-oriented, in-memory transformation engine driven by the M language. In a CAE context it occupies the middle of the pipeline: it consumes raw solver exports (CSV, fixed-width text, Excel workbooks, JSON, folder contents), applies repeatable transformations, and emits a single tabular model that Excel pivot tables or Power BI visuals can consume. Its most relevant capabilities for simulation data are:

• Folder and file enumeration. Folder.Files returns a table of file metadata (name, path, modified date, content) that can be filtered by extension or naming convention, then expanded row by row. This is the mechanism that turns "200 CSV files in a results directory" into one query.
• Parameterized custom functions. A query can be converted into a function (fx) that accepts a file's binary content and returns a normalized table. Invoking it across the file list produces the consolidated result — the standard "function over folder" pattern.
• Schema normalization. Column renaming, type conversion, unpivoting wide result tables into long format, splitting composite keys (e.g., run_012_LC3 into run ID and load case), and merging lookup tables for material or boundary-condition metadata.
• Incremental refresh (Power BI Premium/Fabric). Only new or changed files are reprocessed, which matters when a parametric sweep adds runs daily.
• Query folding. When the source is a foldable database, transformations are pushed to the source. File-based CAE sources generally do not fold, so this benefit is limited to database-backed result stores.

Where Power Query Stops Being the Right Tool

Power Query is not a numerical analysis environment, and treating it as one leads to fragile, slow models. The practical limitations are:

• No native binary solver formats. Proprietary result databases (e.g., .odb, .rst, .op2) require an external extraction step — a solver script, a Python reader, or a vendor API — to produce CSV/JSON first. Power Query cannot parse these directly.
• Row-by-row M is slow. Iterating with List.Accumulate or per-row custom functions over large result sets degrades badly. Vectorized table operations and Table.TransformColumns are fast; loops are not.
• No matrix or interpolation math. Modal assurance criteria, frequency interpolation, or curve fitting belong in Python, MATLAB, or the solver's post-processor. Power Query can carry the numbers but should not compute them.
• Memory ceiling. The engine loads data into memory. Multi-gigabyte transient result dumps should be reduced at extraction time (e.g., export only peak values or a sampled time history) rather than loaded wholesale.
• Limited error diagnostics. A malformed file in a folder query surfaces as a row-level error that can silently propagate. Explicit error handling with try ... otherwise and an error-audit column is essential.

Integration with Excel and Power BI

The same M query can serve both hosts, but the delivery characteristics differ. In Excel, the query loads to a table or the Data Model; refresh is manual or on-open, and the workbook is the distribution unit — convenient for a single engineer reviewing a study, less so for a team. In Power BI, the query is published to a semantic model, refresh is scheduled via the gateway, and the same model feeds dashboards for many consumers. A common split: engineers iterate in Excel against a local folder; the validated query is then promoted to a Power BI dataset pointing at a shared network path or data lake.

Two integration details deserve attention. First, path handling: hard-coded absolute paths break when the model moves between machines. Parameterize the root path as a query parameter and, in Power BI, bind it to a gateway-mapped location. Second, type stability: set explicit types on every output column, because an empty result set or a changed header in one file can otherwise shift the schema and break downstream visuals.

A Worked Consolidation Example

Consider a modal study with 120 runs, each exporting results_runNNN.csv with columns mode, frequency_Hz, damping_ratio, participation_X. The goal is a single table keyed by run and mode. The M steps are: enumerate the folder, filter to .csv, invoke a parsing function that promotes the header and sets frequency_Hz to type number, extract the run ID from the filename with Text.BetweenDelimiters([Name], "run", ".csv"), then expand. Expected output: 120 × N rows with a RunID column. Verification: row count should equal the sum of modes across all files; a mismatch indicates a file with a different header or a truncated export. Typical failure symptom — a column of Error values after expansion — almost always traces to a type-conversion step applied before the header was promoted, or to a locale mismatch where 1,234.5 is parsed as text.

The correct division of labor is straightforward: extract and reduce inside the solver environment, consolidate and reshape in Power Query, and compute engineering metrics downstream. Used this way, Power Query turns a manual, error-prone spreadsheet assembly into a refreshable, auditable step in the CAE data pipeline.