4. Pivot and Aggregate CAE Results by Load Case and Geometry Variant

Why pivot instead of filter

After the previous cleaning and peak-extraction steps, the CAE batch output is a flat, long-format table: one row per (load case, design variant, node) result record. That shape is ideal for storage and for filtering, but it is poor for comparison. To answer "which design variant survives the 3g bump case with the lowest peak displacement," you need the data reorganized so that each load case and variant pair becomes a single row carrying both a mean and a maximum. Power Query's Table.Group plus a controlled pivot produces exactly that summary, and it does so reproducibly: re-run the query after a new solver batch and the same aggregation logic applies without manual copy-paste.

The task in this case is deliberately narrow. We take a flat results table with columns LoadCase, Variant, NodeID, and Disp_mm, and we produce a summary table with one row per (LoadCase, Variant) containing MeanDisp_mm, MaxDisp_mm, and the node that produced the maximum. We do not re-clean names or units (that was the earlier step) and we do not join mesh metadata (that comes later). The focus is the aggregation and pivot mechanics themselves.

Input shape and the aggregation contract

A representative slice of the flat table looks like this. Values are illustrative teaching data, not measured results.

The aggregation contract is explicit: group by the composite key (LoadCase, Variant); within each group compute the arithmetic mean of Disp_mm and the maximum of Disp_mm; also carry the NodeID associated with that maximum so the peak can be traced back to a physical location. The mean and max must be computed on the same filtered population — if you exclude a node for one statistic, exclude it for both, otherwise the two columns describe different datasets.

Building the query step by step

Start from the cleaned flat table loaded as CAE_Results. The grouping is done with Table.Group, which takes the key columns, an optional output column name, and a list of aggregation definitions. The critical detail is that Table.Group hands each sub-table to the aggregation function, so you can compute several statistics in one pass and even extract the row that produced the maximum.

let
    Source = CAE_Results,
    // Guard: drop rows with no displacement before aggregating
    Clean = Table.SelectRows(Source, each [Disp_mm] <> null),
    // Group by the composite key and compute mean, max, and peak node
    Grouped = Table.Group(
        Clean,
        {"LoadCase", "Variant"},
        {
            {"MeanDisp_mm", each List.Average([Disp_mm]), type number},
            {"MaxDisp_mm",  each List.Max([Disp_mm]),     type number},
            {"PeakNodeID",  each
                let
                    peak = List.Max([Disp_mm])
                in
                    try Table.SelectRows(_, each [Disp_mm] = peak){0}[NodeID]
                    otherwise null,
                type text}
        }
    ),
    // Round for reporting without changing the underlying precision
    Rounded = Table.TransformColumns(Grouped, {
        {"MeanDisp_mm", each Number.Round(_, 3), type number},
        {"MaxDisp_mm",  each Number.Round(_, 3), type number}
    }),
    // Sort so the worst cases surface first
    Sorted = Table.Sort(Rounded, {{"MaxDisp_mm", Order.Descending}})
in
    Sorted

Two points deserve attention. First, the PeakNodeID step uses Table.SelectRows(_, ...) where _ is the current group's sub-table; the try ... otherwise null wrapper protects against ties or empty groups so the query does not fail on a batch that happens to contain a single-node group. Second, rounding is applied as a separate transform after aggregation, so the mean and max are computed at full precision and only the displayed values are rounded — rounding before aggregation would bias the mean.

From grouped table to a true pivot

The grouped output is already a summary, but if you want load cases as columns and variants as rows (or vice versa) for a side-by-side comparison, add a pivot on top. Pivot the LoadCase key so each load case becomes a column, keeping Variant as the row key:

    // Pivot load cases into columns; aggregate the already-computed max
    Pivoted = Table.Pivot(
        Table.TransformColumnTypes(Grouped, {{"LoadCase", type text}}),
        List.Distinct(Table.Column(Grouped, "LoadCase")),
        "LoadCase",
        "MaxDisp_mm",
        List.Max
    )

Because the grouped table has exactly one row per (LoadCase, Variant), the List.Max aggregator in Table.Pivot is a pass-through — it does not change values, it only satisfies the pivot's requirement for an aggregator. This is the safe pattern: aggregate first with Table.Group, then pivot the pre-aggregated result. Pivoting the raw flat table directly would force the pivot to aggregate across nodes, and you would lose the ability to attach the peak node ID.

Reading the result

The final table has one row per variant and one column per load case, with the maximum displacement in each cell. A worked check on the sample rows above: for Bump_3g/V1 the mean of 2.41 and 3.07 is 2.74 mm and the max is 3.07 mm at node N1043; for Bump_3g/V2 the single record gives mean = max = 2.18 mm at N1042. In the pivoted view, the Bump_3g column reads 3.07 for V1 and 2.18 for V2, immediately showing V2 as the lower-displacement variant under that load case. These are illustrative values derived directly from the sample rows, not solver output.

Verification and common failure modes

Verify the query against the flat table with three checks. First, row count: the grouped table must have exactly as many rows as there are distinct (LoadCase, Variant) pairs in the cleaned input — a mismatch means a key column was omitted from the group. Second, max consistency: for any group, MaxDisp_mm must be greater than or equal to MeanDisp_mm; if it is not, the two statistics were computed on different populations. Third, traceability: look up the PeakNodeID in the flat table and confirm its Disp_mm equals MaxDisp_mm for that group.

• Duplicate rows after grouping — a key column such as Variant was left out of the group list, so distinct variants collapsed into one row. Add the missing key.
• PeakNodeID returns null — the group had a tie at the maximum and the try wrapper caught it, or the group was empty. Inspect the group; if ties are legitimate, decide a tie-break rule (e.g., lowest NodeID) and encode it.
• Pivot cells all null — the pivot column name passed to Table.Pivot does not match the actual column, or the value column is not numeric. Confirm the column names and types before pivoting.
• Mean looks wrong after rounding — rounding was applied before aggregation. Move the Number.Round transform after Table.Group.

Once the grouped and pivoted outputs pass these checks, the summary is ready to feed downstream comparison or reporting. The same query re-runs unchanged on the next solver batch, which is the point of doing the aggregation in Power Query rather than in a spreadsheet.