4. Extracting run identifiers and metadata from folder structures and file names using Power Query transformations.

In a multi-run CAE workflow, the folder tree and file names are usually the only reliable record of what a result actually represents. Solver output files rarely carry a self-describing header that says "this is run 07, baseline suspension, 3.2 mm bump stop, mesh level 2." That information lives in the directory path and the naming convention your team agreed on. Power Query's job in this chapter is to turn that implicit, human-readable structure into explicit, queryable columns that every downstream step can rely on.

The core idea is straightforward: treat the full file path as a delimited string, split it into its structural segments, and then map those segments onto named metadata fields. The difficulty is not the splitting itself but the discipline around it — deciding which segment carries which meaning, handling runs that deviate from the convention, and making the extraction robust enough that a single malformed folder name does not silently corrupt the entire dataset.

Establishing the Path Contract First

Before writing any transformation, define the path contract explicitly. A workable convention for CAE batch runs looks like this:

D:\CAE\ProjectA\Run_07\baseline\bumpstop_3p2mm\mesh_L2\results\job_07_final.dat

Reading left to right, the segments after the project root carry: run identifier (Run_07), configuration label (baseline), variant parameter (bumpstop_3p2mm), mesh level (mesh_L2), and then the standard results folder and file. The file name itself repeats the run number and adds a status token (final). This redundancy is deliberate — it lets you cross-check that the folder-derived run ID and the file-name-derived run ID agree, which is one of the cheapest data-quality checks available.

Write the contract down as a table before touching Power Query. Ambiguity here becomes a debugging nightmare later, because a mis-mapped column produces plausible-looking but wrong aggregations rather than an obvious error.

Splitting the Folder Path into Segments

Power Query exposes the full path through the Folder.Files function, which returns a Folder Path column and a Name column among others. The most controllable approach is to add a custom column that splits the folder path on the backslash delimiter and then index into the resulting list. In M:

let
    Source = Folder.Files("D:\CAE\ProjectA"),
    AddSegments = Table.AddColumn(Source, "Segments",
        each Text.Split([Folder Path], "\"), type list),
    AddRunId = Table.AddColumn(AddSegments, "RunID",
        each [Segments]{3}?, type text)
in
    AddRunId

Two details matter here. First, the index depends on where the project root sits in the absolute path; indexing from the left is fragile if the drive letter or root depth ever changes. A more robust pattern is to split, then use List.PositionOf to locate a known anchor segment such as "ProjectA" and index relative to it. Second, the ? after the index is the optional-field operator: if a path is shallower than expected, the expression returns null instead of throwing. That single character is the difference between a query that flags bad rows and one that fails entirely.

Once the segment list exists, extract each metadata field with its own indexed column, or better, use Table.TransformColumns with a record-building function so all fields are derived in one pass and stay visually grouped.

Parsing the File Name

File names follow a different delimiter convention, typically underscores. Split [Name] on "_" and map positions to fields. For job_07_final.dat, position 1 is the run number, position 2 is the status token, and the extension is stripped separately with Text.BeforeDelimiter([Name], ".") or by using the Extension column that Folder.Files already provides.

Do not assume every file has the same number of tokens. A file named job_07_final_rev2.dat has an extra segment. Rather than hard-coding positions, consider a small mapping table that lists token index, field name, and expected pattern, then join or iterate against it. For most teams a fixed positional parse plus a validation column is sufficient and far easier to maintain.

Type Conversion and Normalization

Extracted text is not yet usable metadata. Convert the run number to an integer with Number.FromText so that sorting is numeric rather than lexicographic — otherwise Run_10 sorts before Run_2. Normalize the variant parameter by replacing the encoded decimal marker: Text.Replace([Variant], "p", ".") turns bumpstop_3p2mm into 3.2 after a further split on mm. Keep the original string in a separate column so the transformation is auditable.

Mesh level deserves the same treatment: strip the mesh_L prefix and convert to an integer. Configuration labels such as baseline and optimized should be mapped to a controlled vocabulary via a lookup table rather than trusted as free text, because a typo like basline will otherwise create a phantom category in every downstream pivot.

Validating the Extraction

Add a validation column that compares the folder-derived run ID with the file-name-derived run ID and returns a status string. Rows where they disagree, or where any required field is null, should be filtered into a separate "quarantine" query rather than dropped silently. This is the practical payoff of the redundancy built into the naming convention: mismatches usually indicate a file copied into the wrong run folder, which is exactly the kind of error that ruins a comparison study if it goes unnoticed.

Handling Convention Violations

Real folders drift. Someone creates Run7 instead of Run_07, or nests an extra archive level. Two strategies keep this manageable. The first is a normalization step that applies Text.Trim, case folding, and a regex-based extraction using Text.Match patterns such as "Run_?(\d+)" to pull the number regardless of formatting. The second is an explicit exception table listing known deviations and their corrected values, joined into the main query. Prefer the regex approach for numeric fields and the exception table for categorical ones, since categories are where silent errors hide.

The diagram below shows how a single file path is decomposed into structural segments, mapped to named metadata fields, and then validated before it enters the consolidated results table.

What the Output Should Look Like

After extraction, the consolidated table should carry one row per result file with columns for RunID, Config, BumpStop_mm, MeshLevel, FileRunID, ValidationStatus, and the original Folder Path and Name. A representative row from the example above would show RunID = 7, Config = baseline, BumpStop_mm = 3.2, MeshLevel = 2, FileRunID = 7, ValidationStatus = OK.

To verify the extraction, apply a group-by on RunID and count rows per run. If a run that should contain twelve result files shows eleven, or if the quarantine query returns rows, investigate before proceeding. A quick pivot of Config against RunID also exposes phantom categories caused by typos. These checks take a minute and catch the majority of path-convention errors before they propagate into comparison plots.

With run identifiers and metadata now materialized as typed columns, every subsequent transformation — filtering to a mesh level, pivoting configurations against each other, or joining solver output to test measurements — can operate on clean keys instead of re-parsing strings. That separation is what makes the rest of the automation maintainable.