3. Filter and Extract Peak Stress Records from Transient CAE Simulations

Problem framing: peak stress is a per-group reduction, not a global maximum

A transient CAE run produces a time-history table in which each row is a sample: one node, one load case, one time step, one stress value. The task in this case is to reduce that long table to one row per load case, carrying the maximum stress value together with the time stamp and node ID at which it occurred. The subtlety is that Table.Max on a single column returns only the value; the associated time and node must be recovered from the same row. Any approach that computes the maximum and then re-filters the table risks returning multiple rows when two nodes tie, so the reduction must be done per group with an explicit tie-breaking rule.

Assume the consolidated table from the batch import already exists as a query named TransientResults with the standardized schema: LoadCase (text), NodeID (Int64), Time_s (Decimal), Stress_MPa (Decimal), and SourceFile (text). The example below uses a small teaching dataset so the expected output can be checked by hand.

Teaching dataset and expected result

Six samples across two load cases are enough to expose the grouping and tie logic:

Expected summary: LC01 → 241.7 MPa at 0.20 s, node 1042 (lowest NodeID wins the tie); LC02 → 305.2 MPa at 0.15 s, node 1042. The tie in LC01 is deliberate: it forces the query to define a deterministic winner rather than silently returning two rows.

Building the grouped reduction in Power Query

Start from TransientResults, drop rows with a null Stress_MPa (solver output occasionally writes blank lines at restart boundaries), and enforce types before grouping so that numeric comparison is not done on text:

let
    Source = TransientResults,
    CleanRows = Table.SelectRows(Source, each [Stress_MPa] <> null and [LoadCase] <> null),
    Typed = Table.TransformColumnTypes(CleanRows, {
        {"LoadCase", type text},
        {"NodeID", Int64.Type},
        {"Time_s", type number},
        {"Stress_MPa", type number}
    }),
    Grouped = Table.Group(Typed, {"LoadCase"}, {
        {"PeakRow", each
            let
                MaxStress = List.Max(_[Stress_MPa]),
                Candidates = Table.SelectRows(_, each [Stress_MPa] = MaxStress),
                Winner = Table.First(
                    Table.Sort(Candidates, {{"NodeID", Order.Ascending}, {"Time_s", Order.Ascending}})
                )
            in
                Winner,
            type record
        }
    }),
    Expanded = Table.ExpandRecordColumn(Grouped, "PeakRow",
        {"NodeID", "Time_s", "Stress_MPa", "SourceFile"},
        {"PeakNodeID", "PeakTime_s", "PeakStress_MPa", "SourceFile"}),
    Sorted = Table.Sort(Expanded, {{"LoadCase", Order.Ascending}})
in
    Sorted

Three details carry the correctness of this query. First, List.Max(_[Stress_MPa]) operates on the group's own column, so the maximum is per load case, not global. Second, Candidates re-selects every row matching that maximum, which is what makes the tie visible instead of hidden. Third, Table.Sort on NodeID then Time_s makes the winner deterministic; without it, Table.First returns whatever row order the source happened to have, and the summary would change between refreshes.

What the reduction looks like as a data flow

The diagram below shows the same logic as a per-group pipeline: the long time-history table is partitioned by LoadCase, each partition is reduced to its maximum, ties are resolved by the sort key, and the winning records are re-expanded into the summary schema.

Verification and failure symptoms

Check the summary against three independent facts before publishing it. The row count must equal the number of distinct LoadCase values in the source; a mismatch means a group was dropped or duplicated. Each PeakStress_MPa must be greater than or equal to every stress value for that load case in the source, which you can confirm with a quick Table.Group on the raw table computing List.Max only. And each PeakTime_s must exist in the source for that load case and node, which catches the common error of pairing a maximum with a time from a different row.

• Duplicate rows per load case: the tie was not resolved. Confirm Table.First is applied after Table.Sort, not before.
• Peak value looks plausible but time is wrong: the maximum was computed on one column and the time pulled from a separate Table.Max call. Keep the whole row together as a record.
• Peak changes on every refresh: no deterministic sort key. Add NodeID and Time_s to the sort.
• All values identical across load cases: grouping key is missing or the LoadCase column was typed as a constant. Re-check the Table.Group key list.

Deliverable: peak stress summary schema

The output query PeakStressSummary should be loaded to a worksheet or data model with the following columns and types, which downstream cases can consume without further reshaping:

With this table in place, the peak values, their time stamps, and their node locations are all traceable to a single source row per load case, and the tie-breaking rule is explicit in the query rather than implicit in file order.