2. Clean and Standardize Variable Names and Units in CAE Result Tables

Why header and unit drift breaks downstream queries

In a batch CAE run, the same solver output field can arrive under several spellings and unit systems. One file writes DISP_X in mm, another writes disp-x in m, and a third writes Displacement X in mm. Stress may appear as vonMises in MPa or VON_MISES in Pa. If you leave these untouched, every later step — filtering peak stress, pivoting by load case, joining node tables — silently produces wrong numbers or fails to match columns. The goal of this case is to make the combined table carry one canonical schema: stable column names, SI base units, and enforced data types.

We work on the consolidated table produced by the previous import step. Assume a query named RawResults already holds all rows from the batch directory, with a Source.File column for provenance. The transformation below is written as a single Power Query M script so it can be pasted into the Advanced Editor and re-run on every new batch.

Canonical schema decisions

Before writing any M, fix the target contract. Ambiguity here is what causes rework later.

Two rules keep this maintainable. First, the canonical name embeds the unit (_m, _pa, _s) so a reader never has to guess. Second, unit conversion happens before the type change, because text like "1.2e-3" must be parsed as a number first.

Detecting which unit each file used

Files rarely state their unit in the header alone. A reliable pattern is to read the unit from a companion line or from the file name convention, then carry it as a per-row attribute. In this batch the file names follow run_<id>_<unit_system>.csv, where unit_system is mm_MPa or m_Pa. We extract it once per file and keep it as a column so the conversion factor can be applied row-wise.

let
    Source = RawResults,
    // 1. Derive the unit system from the file name, e.g. run_07_mm_MPa.csv
    AddUnitSystem = Table.AddColumn(Source, "unit_system",
        each let
                name = [Source.File],
                parts = Text.Split(Text.BeforeDelimiter(name, ".csv"), "_"),
                n = List.Count(parts)
            in
                if n >= 2 then parts{n-2} & "_" & parts{n-1} else "unknown",
        type text),

    // 2. Normalize headers: trim, lowercase, strip spaces and punctuation
    NormalizeHeaders = Table.TransformColumnNames(AddUnitSystem,
        each Text.Lower(Text.Remove(_, {" ", "-", "(", ")", "."}))),

    // 3. Map every known variant to the canonical name
    RenameMap = {
        {"dispx", "disp_x_m"},
        {"displacementx", "disp_x_m"},
        {"dispy", "disp_y_m"},
        {"displacementy", "disp_y_m"},
        {"vonmises", "stress_vm_pa"},
        {"stressvonmises", "stress_vm_pa"},
        {"nodeid", "node_id"},
        {"node", "node_id"},
        {"loadcase", "load_case"},
        {"lc", "load_case"},
        {"time", "time_s"},
        {"times", "time_s"}
    },
    Renamed = Table.RenameColumns(NormalizeHeaders, RenameMap, MissingField.Ignore),

    // 4. Convert units to SI base before typing
    ConvertUnits = Table.TransformColumns(Renamed, {
        {"disp_x_m", each if [unit_system] = "mm_MPa" then _ / 1000 else _, type number},
        {"disp_y_m", each if [unit_system] = "mm_MPa" then _ / 1000 else _, type number},
        {"stress_vm_pa", each if [unit_system] = "mm_MPa" then _ * 1000000 else _, type number}
    }),

    // 5. Enforce final data types
    Typed = Table.TransformColumnTypes(ConvertUnits, {
        {"node_id", Int64.Type},
        {"load_case", type text},
        {"time_s", type number},
        {"disp_x_m", type number},
        {"disp_y_m", type number},
        {"stress_vm_pa", type number}
    }),

    // 6. Drop helper column and keep a stable column order
    Final = Table.SelectColumns(Typed,
        {"Source.File", "load_case", "node_id", "time_s",
         "disp_x_m", "disp_y_m", "stress_vm_pa"})
in
    Final

How the transformation flows

The diagram below shows the per-row path from a raw file to the canonical record, including the branch where the unit system decides the conversion factor. It is the mental model to keep when debugging a wrong magnitude.

Verifying the result before it reaches analysis

Do not trust the query just because it runs. Add a small validation block that fails loudly when the schema drifts. The following checks are cheap and catch the most common batch problems.

let
    Checked = Final,
    // a) No nulls in key columns
    NullKeys = Table.RowCount(Table.SelectRows(Checked,
        each [node_id] = null or [load_case] = null)),
    // b) Magnitude sanity: displacement in metres should be small
    MaxDisp = List.Max(Table.Column(Checked, "disp_x_m")),
    // c) Stress should be positive and in a plausible Pa range
    MinStress = List.Min(Table.Column(Checked, "stress_vm_pa")),
    // d) Every source file must still be represented
    FileCount = List.Count(List.Distinct(Table.Column(Checked, "Source.File")))
in
    [NullKeyRows = NullKeys, MaxDisp_m = MaxDisp,
     MinStress_Pa = MinStress, DistinctFiles = FileCount]

Interpret the output against expectations. NullKeyRows must be 0; a non-zero value means a header variant was not mapped and the column arrived empty. MaxDisp_m for a millimetre-scale model should be on the order of 1e-2 to 1e-1; a value near 12.5 means the mm → m division never applied, usually because unit_system was parsed as unknown. MinStress_Pa below zero points to a sign convention issue or a mis-parsed text field. DistinctFiles must equal the number of files in the batch directory.

Reusable checklist for the next batch

• Confirm the canonical name list and unit suffixes are agreed before editing M.
• Extend RenameMap for every new header variant; never let an unmapped column pass silently.
• Keep unit conversion ahead of type enforcement.
• Re-run the validation block and compare DistinctFiles to the directory count.
• Spot-check one known row by hand: 12.5 mm must read 0.0125 in disp_x_m.

With this query in place, the combined table has a single schema regardless of which solver run produced a row, so peak-stress filtering and pivot aggregation can rely on fixed column names and SI magnitudes.