4. Dynamic file path handling

In a multi-run CAE workflow, the single most fragile part of a Power Query solution is the file path. A hard-coded path such as C:\Projects\Bracket_v3\Run_01\results\stress.csv works exactly once. The moment a solver writes to Run_02, or the project folder is renamed, or a colleague opens the workbook on a machine with a different drive letter, the query breaks. This chapter shows how to replace hard-coded paths with a parameter-driven, function-based architecture that resolves run folders dynamically — including runs stored in nested directories whose naming conventions vary between campaigns.

Why a Single Parameter Is Not Enough

The naive approach is to create one text parameter, RootPath, and concatenate it with a fixed relative path. That handles relocation but not variability. Real CAE campaigns typically exhibit at least three kinds of variation:

• Depth variation — some runs sit at Project\Run_01\, others at Project\Loadcase_A\Run_01\, and legacy batches at Project\2023\Q4\Loadcase_A\Run_01\.
• Naming variation — run folders may be Run_01, run-1, R01, or Run_01_baseline depending on the solver wrapper version.
• File placement variation — the result file may be stress.csv, stress_results.csv, or nested one level deeper under post\.

A robust design therefore separates where to search (a root parameter), how to recognize a run (a naming rule), and how to extract the file (a reusable function). The diagram below shows this three-layer architecture and how data flows from the parameter layer through the discovery function to the final combined table.

Building the Discovery Function

The discovery function answers one question: given a root folder and a naming rule, which subfolders are runs? The key decision is to enumerate files recursively rather than folders, because Folder.Files returns the full path of every file and lets you filter on the folder portion. This avoids the combinatorial cost of walking nested directories level by level.

// fnGetRunFolders
(RootPath as text, RunPattern as text) as list =>
let
    AllFiles   = Folder.Files(RootPath),
    FolderCol  = Table.AddColumn(AllFiles, "FolderPath",
                    each Text.BeforeDelimiter([Folder Path], "\", {0, RelativePosition.FromEnd})),
    // keep only folders whose leaf name matches the run pattern
    LeafName   = Table.AddColumn(FolderCol, "Leaf",
                    each Text.AfterDelimiter([FolderPath], "\", {0, RelativePosition.FromEnd})),
    Matched    = Table.SelectRows(LeafName, each Text.Matches([Leaf], RunPattern)),
    Distinct   = List.Distinct(Matched[FolderPath])
in
    Distinct

With RunPattern = "^(Run|run|R)[-_]?\d{2}.*$", this function accepts Run_01, run-1, R01, and Run_01_baseline in a single pass, at any nesting depth. The regex is a parameter, so a new campaign with a different convention requires no code edit — only a parameter change.

Building the Extraction Function

The extraction function takes a single run folder and returns a typed table with provenance columns. It must tolerate the file being either directly in the run folder or one level deeper under post\:

// fnReadRunResult
(RunFolder as text, ResultFileName as text) as table =>
let
    Candidates = Folder.Files(RunFolder),
    Hit        = Table.SelectRows(Candidates,
                    each Text.EndsWith([Name], ResultFileName, Comparer.OrdinalIgnoreCase)),
    Guard      = if Table.RowCount(Hit) = 0
                 then error "Result file not found in " & RunFolder
                 else Hit,
    Content    = Csv.Document(Guard{0}[Content], [Delimiter=",", Encoding=65001]),
    Promoted   = Table.PromoteHeaders(Content, [PromoteAllScalars=true]),
    RunID      = Text.AfterDelimiter(RunFolder, "\", {0, RelativePosition.FromEnd}),
    Tagged     = Table.AddColumn(Promoted, "RunID", each RunID, type text),
    WithPath   = Table.AddColumn(Tagged, "SourcePath", each RunFolder, type text)
in
    WithPath

The explicit error guard is deliberate. Silent empty tables are the most common cause of misleading dashboards in multi-run workflows: a missing run simply disappears from the aggregate, and nobody notices until a review meeting. Failing loudly with the offending folder path turns a data-quality problem into an immediate, locatable message.

Composing the Two Functions

The final query is now a short, declarative composition. It resolves the run list, invokes the extraction function per run, and expands the results:

let
    Runs      = fnGetRunFolders(RootPath, RunPattern),
    AsTable   = Table.FromList(Runs, Splitter.SplitByNothing(), {"RunFolder"}),
    Extracted = Table.AddColumn(AsTable, "Data",
                    each fnReadRunResult([RunFolder], ResultFileName)),
    Expanded  = Table.ExpandTableColumn(Extracted, "Data",
                    {"RunID","StressMax","DisplacementMax","SourcePath"})
in
    Expanded

Note that Table.AddColumn with a function call is evaluated lazily per row. For campaigns with hundreds of runs, wrap the expansion in Table.Buffer only if you observe repeated re-evaluation in the query plan — buffering large result sets prematurely increases memory pressure without benefit.

Handling Variable Naming Conventions in Practice

When conventions differ between campaigns rather than within one, the cleanest pattern is a small mapping table stored as a query, not as nested if logic. For example:

Each row can be passed to the same two functions, and the outputs appended with Table.Combine. This keeps the logic identical across campaigns while isolating the convention differences in data — the single most maintainable arrangement for long-lived CAE reporting workbooks.

Verification and Common Failure Symptoms

After wiring the functions, verify with a small, known campaign before scaling up. A practical check sequence:

1. Invoke fnGetRunFolders alone and confirm the returned list length equals the number of run folders you can count manually in Explorer.
2. Invoke fnReadRunResult on one known folder and confirm the column headers match the CSV's first row and that RunID equals the leaf folder name.
3. Expand the full query and confirm row count equals runs × rows-per-result-file.

Typical symptoms and their causes: an empty list from the discovery function almost always means the regex is anchored incorrectly (for example, ^Run without allowing the run- variant) or the root path uses a trailing backslash that shifts the leaf extraction. A "Result file not found" error with a correct-looking path usually indicates a case-sensitivity mismatch on a network share — the Comparer.OrdinalIgnoreCase argument in the extraction function prevents this. Duplicate rows after expansion point to overlapping root paths in the campaign mapping table, which List.Distinct on the folder list will resolve.

With parameters, a discovery function, and an extraction function in place, the workbook becomes portable: relocating the project, renaming a run, or adding a new campaign requires editing a parameter or a mapping row — never the query logic itself. This is the foundation on which the aggregation and comparison chapters build.