3. Connecting to CAE result files

In a multi-run CAE workflow, the hardest part of automation is rarely the transformation logic itself — it is reliably locating and ingesting the heterogeneous files that each solver run produces. A single study may generate dozens of run folders, each containing a CSV export of nodal results, an Excel workbook with summary metrics, and a plain-text solver log or ASCII output. Power Query must be taught to find these files by pattern rather than by hard-coded path, so that adding a new run directory requires no query edits. This section covers the three connector families you will use (CSV, Excel workbook, and text/line-based solver output), how to enumerate files with the Folder connector, and how to parameterize paths so the same query scales from three runs to three hundred.

Why folder-level ingestion beats per-file connections

Connecting to a single CSV with Data > Get Data > From Text/CSV produces a query bound to one absolute path. When a new run appears, that query is stale. The Folder connector (Get Data > From Folder) instead returns a table of file metadata — Content, Name, Extension, Folder Path, Date modified — and lets you filter that table before expanding any file contents. This two-stage pattern (enumerate, then expand) is the backbone of multi-run ingestion: you filter by extension and naming convention first, so Power Query only parses the files you actually want, which keeps refresh times predictable as run counts grow.

A practical directory convention makes this filtering trivial. Assume a study root such as D:\CAE\Study_07\ containing run folders named run_001, run_002, and so on, each with a consistent internal layout:

Study_07/
├─ run_001/
│  ├─ nodal_results.csv
│  ├─ summary_metrics.xlsx
│  └─ solver_output.txt
├─ run_002/
│  ├─ nodal_results.csv
│  ├─ summary_metrics.xlsx
│  └─ solver_output.txt
└─ run_003/ ...

Consistency in file names across runs is what allows a single expansion step to combine all runs into one table. If a solver writes nodal_results.csv in one run and Nodal_Results_v2.csv in another, the combined query will silently drop rows or fail to match columns. Treat file naming as part of the automation contract.

Enumerating and filtering the folder

Point the Folder connector at the study root. The initial table lists every file in every subfolder. Apply these filters in order:

1. Filter Extension to .csv (or .xlsx, .txt) depending on which artifact you are ingesting — keep one query per artifact type rather than mixing them.
2. Filter Name to the expected pattern, e.g. nodal_results.csv, using Text Filters > Equals.
3. Optionally filter Folder Path to exclude archive or scratch directories such as run_000_template.

After filtering, the table has one row per run. Before expanding, add a column that captures the run identity, because the file contents themselves usually do not carry it. Use Add Column > Custom Column with an expression that extracts the run folder name from the path:

RunID = Text.BetweenDelimiters([Folder Path], "Study_07\", "\")

This yields run_001, run_002, etc. Keeping RunID as a column before expansion means every result row is traceable to its originating run — essential when you later pivot, compare, or flag outliers. Do not rely on row order to identify runs; it is not stable across refreshes.

Expanding CSV content

With the filtered table in place, use Combine Files or expand the Content column directly. For CSV, the recommended approach is to add a custom column that parses each file explicitly, giving you control over delimiter, encoding, and header handling:

CsvData = Csv.Document([Content], [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])

Then expand the resulting column and promote the first row to headers. If all runs share an identical schema, Combine Files handles this in one dialog and is faster to set up. If schemas drift between runs (for example, a solver version adds a column), explicit Csv.Document plus a defined column set makes the mismatch visible as errors rather than silent nulls. For CAE nodal exports, watch for locale-dependent decimal separators: a file written with comma decimals will parse incorrectly under a comma delimiter. Set Culture explicitly in Csv.Document when the solver's output locale is known.

Excel workbooks and text-based solver output

Excel files need one extra decision: which sheet and which range. Use Excel.Workbook([Content], true) to read the workbook, then filter the Item column to the target sheet name (e.g. Summary) and expand Data. Avoid connecting to a fixed cell range like Sheet1!A1:F50; instead read the whole sheet and filter rows afterward, so that a run producing more rows does not get truncated. If summary workbooks place metrics in a key-value layout rather than a tabular one, unpivot after expansion so each metric becomes a row with its RunID.

Text-based solver output is the least structured and needs the most care. A typical log line might read:

RUN 002 | MAX_STRESS 214.7 MPa | MAX_DISP 1.83 mm | ITER 14 | CONVERGED

Read it with Lines.FromBinary([Content], null, null, 65001) to get a list of lines, convert to a table, then extract fields with Text.BetweenDelimiters or split on |. Because solver logs often contain headers, banners, and warnings, filter lines to those matching a stable prefix (e.g. starting with RUN) before parsing. This keeps the parser resilient to log noise.

Parameterizing the root path

Hard-coding D:\CAE\Study_07\ inside the query defeats portability. Create a Power Query parameter, StudyRoot, of type Text, and reference it in the Folder connector's path. Parameters can be edited from Home > Manage Parameters or driven by a small configuration table in a workbook, so that switching studies or moving to a network share requires no query logic changes. A second parameter, ArtifactName, can hold nodal_results.csv so the same query template serves different artifact types. When the root path is a network location, prefer a mapped drive or UNC path that all refresh machines can resolve; a user-specific drive letter will fail on a scheduled refresh account.

Verifying the connection before building transformations

After expansion, check three things before proceeding: the row count should equal (runs × rows per run) for CSV data; the distinct count of RunID should equal the number of run folders; and no column should be entirely null, which usually signals a header or delimiter mismatch. A quick diagnostic is to group by RunID and count rows — a run with zero rows means its file failed to parse, often due to encoding or a changed delimiter. Typical failure symptoms and their causes: all values in one column become Error (type conversion against unexpected text), rows multiply unexpectedly (a nested table expanded without matching keys), and refresh slows dramatically (the folder filter was applied after expansion instead of before). Fix the filter order first; it is the most common performance and correctness issue in folder-based ingestion.

A reusable connection checklist

The following table captures the decisions that most often cause multi-run ingestion to fail on refresh, and the concrete setting that resolves each. Treat it as a pre-flight check before building any transformation on top of the connection.

Once these connections are stable, the downstream work — type enforcement, unit normalization, and cross-run comparison — operates on a single clean table keyed by RunID. The investment in folder-level enumeration and path parameters pays off precisely when the study grows: adding run_004 to the directory and refreshing is the entire workflow, with no query edits and no risk of a stale absolute path silently pointing at the wrong run.