3. Setting up a run-based data source

CAE post-processing rarely produces a single tidy file. A typical multi-run study leaves behind a directory tree in which each solver run writes its own result files, and the analyst's job is to turn that tree into one coherent table. Power Query is well suited to this because it can treat a folder as a data source, enumerate its contents, filter by file type, and then combine the surviving files into a single query. This section builds that run-based data source from the ground up, using a representative directory layout and a mix of CSV and whitespace-delimited text files.

The run-based folder layout

Before writing any query, decide what a "run" is and how it is represented on disk. A workable convention is one subfolder per run, named with a stable run identifier, and a fixed set of result files inside each. For example:

D:\CAE\Study_A\
├── run_001\
│   ├── summary.csv
│   ├── forces.csv
│   └── residuals.txt
├── run_002\
│   ├── summary.csv
│   ├── forces.csv
│   └── residuals.txt
├── run_003\
│   ├── summary.csv
│   ├── forces.csv
│   └── residuals.txt
└── notes.txt

The run identifier lives in the folder name, not in the file contents. That is deliberate: it means the query can derive the run label from the path rather than relying on every solver to write it into the file. The notes.txt at the study root is a human note, not a result file, and the query must exclude it. This layout is the contract the rest of the section depends on.

Connecting to the folder

In Power Query, use Get Data → Folder and point it at D:\CAE\Study_A. Power Query returns a table with one row per file and a set of metadata columns, including Content (a binary column), Name, Extension, Folder Path, and Date modified. Do not click "Combine" immediately. The default combine dialog assumes a single homogeneous file shape and will fail or silently mis-parse when CSV and text files are mixed. Instead, load the folder as a plain table and shape it explicitly.

Rename the query to something meaningful, such as RunFiles, and keep it as a staging query. A clean pattern is to have one staging query that lists and filters files, and a second query that expands and combines them. This separation makes debugging far easier: when a combine step fails, you can inspect the staging query to confirm exactly which files were selected.

Filtering to the result files you actually want

The raw folder listing includes every file, including the root-level notes.txt. Filter on Extension to keep only .csv and .txt, then filter on Name to exclude files that are not results. A robust approach is to filter by an explicit allow-list rather than by exclusion, because new stray files appear over time:

let
    Source = Folder.Files("D:\CAE\Study_A"),
    KeepExt = Table.SelectRows(Source,
        each List.Contains({".csv", ".txt"}, [Extension])),
    KeepNames = Table.SelectRows(KeepExt,
        each List.Contains({"summary.csv", "forces.csv", "residuals.txt"}, [Name])),
    AddRunId = Table.AddColumn(KeepNames, "RunId",
        each Text.BeforeDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd}))
in
    AddRunId

The AddRunId step is the key move. [Folder Path] ends with a trailing backslash, so taking the text before the last delimiter yields the run folder name, for example run_002. That column becomes the join key that ties every result row back to its run. Verify it by previewing the column: every row should show a run identifier, and no row should show the study root name.

If your runs are nested more deeply, for example Study_A\mesh_fine\run_002\, adjust the delimiter position or split on the full path and take the appropriate segment. Confirm the result visually before proceeding; a wrong run identifier is the most common cause of mislabeled results downstream.

Combining CSV and text files

CSV and whitespace-delimited text files need different parsing, so handle them in two branches and then append the results. For the CSV branch, filter Extension = ".csv", then use Combine Files or expand Content with Csv.Document. For the text branch, filter Extension = ".txt" and parse with Csv.Document using a delimiter of a single space or a tab, depending on how the solver writes residuals.

let
    CsvRows = Table.SelectRows(RunFiles, each [Extension] = ".csv"),
    CsvExpanded = Table.AddColumn(CsvRows, "Data",
        each Csv.Document([Content], [Delimiter = ",", Encoding = 65001])),
    CsvTable = Table.ExpandTableColumn(CsvExpanded, "Data",
        {"iteration", "value"}, {"iteration", "value"}),

    TxtRows = Table.SelectRows(RunFiles, each [Extension] = ".txt"),
    TxtExpanded = Table.AddColumn(TxtRows, "Data",
        each Csv.Document([Content], [Delimiter = " ", Encoding = 65001])),
    TxtTable = Table.ExpandTableColumn(TxtExpanded, "Data",
        {"Column1", "Column2"}, {"iteration", "residual"})
in
    TxtTable

Two details matter here. First, set Encoding = 65001 (UTF-8) explicitly; solver output is often ASCII, but mixed encodings across runs will otherwise produce garbled headers. Second, the text branch's column names come from the file's first row. If residuals files have no header, pass Headers = false in the Csv.Document options and rename the generated Column1/Column2 to iteration and residual. Do not assume the two branches share a schema; they usually do not, and appending mismatched columns silently produces nulls.

Appending into one run-level table

Append the two branches with Append Queries as New, then add a SourceFile column so each row records which file it came from. The final table should carry at least RunId, SourceFile, iteration, and the measured value. At this point the query is a genuine run-based data source: one row per observation, with the run identity preserved and the file provenance intact.

Folder.Files D:\CAE\Study_A Content · Name · Extension Filter Extension ∈ {.csv, .txt} Name ∈ {summary, forces, residuals} Add RunId Text.BeforeDelimiter( [Folder Path], "\") RunFiles (staging) one row per file CSV branch Delimiter = "," Text branch Delimiter = " " · Headers = false Csv.Document Encoding = 65001 Csv.Document Encoding = 65001 Append → RunResults RunId · SourceFile · iteration · value / residual Load to model

Verifying the combined result

Do not trust the preview alone. Check three things before loading. First, row counts: if you have three runs and each summary.csv has one row, the appended table should contain exactly three summary rows, not six. Duplicates usually mean the folder was scanned twice or the append step was applied to an already-combined query. Second, RunId coverage: filter RunId for nulls or blanks; any hit indicates a file whose path did not match the expected depth. Third, schema alignment: confirm that value and residual are not both populated on the same row, which would signal that the two branches were merged by position rather than appended by column name.

A common failure symptom is a column of nulls after appending. This almost always means the CSV and text branches produced different column names, so the append created a union of columns instead of stacking rows. Fix it by renaming columns to a shared schema in each branch before appending, not after.

What the finished source should look like

A correct run-based source has these properties: it enumerates only the intended result files, it derives RunId from the folder path rather than from file contents, it parses CSV and text with their respective delimiters and encodings, and it appends into a single table with explicit column names. Once this staging query is stable, every downstream step — pivoting iterations, computing convergence metrics, or comparing runs — operates on a clean, provenance-tagged table. The next practical concern is keeping this source refreshable as new runs are added, which depends on the folder path being parameterized rather than hard-coded.

← Previous 📖 Contents Next →