2. Preparing CAE results for pivot tables

A pivot table is only as trustworthy as the flat table beneath it. CAE post-processors rarely export data in a pivot-ready shape: they emit hierarchical reports, repeated header blocks, mixed units, sentinel values for missing results, and load-case or iteration identifiers buried inside label strings. This chapter covers the transformation from raw solver output to a clean, normalized fact table that Excel can aggregate without ambiguity.

The target data model

Before touching any file, define the destination structure. A pivot-ready CAE dataset is a single rectangular table where each row is one observation and each column is one variable. For structural results, a workable grain is one row per (load case, iteration/subcase, part or node set, result quantity). Every column should hold exactly one data type, and no column should encode two facts at once.

A typical target schema looks like this:

ColumnTypeExampleRule
LoadCaseText (normalized)LC01_BRAKEUppercase, no spaces, fixed prefix
IterationInteger3Numeric, zero-padded only for display
ComponentTextBracket_LHMatches part naming convention
QuantityTextvonMisesControlled vocabulary
ValueDouble248.6SI base or declared unit column
UnitTextMPaSingle canonical unit per quantity
StatusTextOK / MISSINGExplicit flag, never blank

Once this schema is fixed, every cleaning decision becomes a question of "does this row conform?" rather than an ad-hoc judgment.

Importing without corrupting the data

Use Data > Get Data > From Text/CSV rather than double-clicking the file. The Power Query preview lets you set the delimiter, encoding, and—critically—the data type per column before load. Two settings prevent most downstream pain:

  • Set Locale explicitly. A German-locale export writes 248,6; imported under en-US it becomes text or 2486. Choose the locale matching the solver's output, then convert to number.
  • Use Replace Errors or Remove Errors deliberately. Silent error removal hides missing values; replacing with null preserves the row for later flagging.

For fixed-width report files, split columns by character position in Power Query (Split Column > By Positions) rather than by delimiter, since whitespace alignment is unreliable.

Handling multi-row and merged headers

CAE reports frequently stack a title row, a units row, and a two-level header before the data begins. The reliable pattern is: Remove Top Rows until the first genuine header row is at the top, then Use First Row as Headers. If the header spans two rows (group name above, quantity below), promote only the second row and rename columns to a flat convention such as vonMises_MPa. Never leave merged cells in the source—unmerge first, because merged cells export as one value plus blanks and will break any pivot grouping.

A units row left in the data body is the single most common cause of a pivot column silently turning to text. If a column shows a count instead of a sum in the Values area, check for a stray unit string such as MPa in row 1 of the data.

Units: one canonical unit per quantity

Mixed units destroy aggregation. If one load case reports stress in MPa and another in GPa, a SUM is meaningless. Normalize at import time: pick a canonical unit per quantity (MPa for stress, mm for displacement, N for force), then apply a conversion factor column in Power Query before the data reaches the model. Keep the original value and unit in separate audit columns if traceability matters, but the Value column fed to the pivot must be in one unit.

// Power Query: normalize stress to MPa
= Table.TransformColumns(#"Changed Type",
    {{"Value", each if [Unit] = "GPa" then _ * 1000
                    else if [Unit] = "Pa" then _ / 1e6
                    else _, type number}})

Missing values and sentinel codes

Solvers encode "no result" in many ways: NaN, *****, -9999, N/A, or an empty cell. Each must be mapped to a single explicit representation. The recommended approach is a Status column with values OK and MISSING, and a Value of null for missing rows. This keeps the row visible in the pivot (so counts are honest) while excluding it from averages automatically—Excel ignores nulls in AVERAGE but not in COUNT.

Do not replace missing values with zero. A zero stress is a physical claim; a missing stress is an absence of data. Conflating them biases every mean and standard deviation you compute later.

Normalizing load case and iteration identifiers

This is where most CAE datasets fail. Identifiers arrive as Load Case 1, LC1, lc_01, Loadcase-1, and 1—all the same case. Normalize to a single pattern with a fixed prefix and zero-padded number: LC01, LC02. Iterations similarly: Iter 3, iteration_03, it3 become integer 3. Keep the numeric iteration as a number so pivots sort correctly; keep the load case as text so LC10 does not sort before LC2.

A robust normalization step extracts the numeric token and rebuilds the label:

// Extract digits, pad to 2, prefix
= Table.AddColumn(Source, "LoadCaseNorm",
    each "LC" & Text.PadStart(
        Text.Select([RawLabel], {"0".."9"}), 2, "0"))

After normalization, build a small lookup table mapping raw labels to canonical IDs and merge it in. This documents the mapping, survives new exports, and lets you audit which raw strings collapsed into which case.

From Raw CAE Export to Pivot-Ready Fact Table Raw Export Title: Brake Study Units: MPa, mm Load Case 1 | Iter 3 Bracket_LH 248,6 Bracket_RH ***** lc_02 | it4 Bracket_LH 0.251 GPa Bracket_RH 231.4 merged headers mixed units sentinel ***** inconsistent IDs Power Query Steps 1. Set locale (de-DE) 2. Remove top rows 3. Promote headers 4. Unpivot quantity cols 5. Convert units → MPa 6. Map ***** → null 7. Add Status flag 8. Normalize LC / Iter 9. Merge lookup table 10. Set column types Normalized Fact Table LoadCase Iter Component Value Unit Status LC01 3 Bracket_LH 248.6 MPa OK LC01 3 Bracket_RH null MPa MISSING LC02 4 Bracket_LH 251.0 MPa OK LC02 4 Bracket_RH 231.4 MPa OK Grain: one row per (LoadCase, Iter, Component, Quantity). No merged cells. Pivot-safe: numeric Value, text LoadCase, explicit Status, single unit per quantity. Each transformation is a recorded Power Query step — re-runnable on the next solver export.

Verification before you pivot

Load the cleaned table to the Data Model, then run three checks before building any pivot. First, confirm row count: the fact table row count should equal (load cases × iterations × components × quantities) minus any rows legitimately dropped. Second, check the Status distribution—if MISSING exceeds a few percent, investigate the solver run rather than proceeding. Third, drop Value into a pivot's Values area and confirm the default aggregation is Sum, not Count; a Count default means a text value slipped into a numeric column.

Save the Power Query steps as a named query and point it at a folder rather than a single file. When the next iteration batch arrives, refresh once and every downstream pivot updates against the same normalization rules—no manual re-cleaning.

The payoff of this discipline is that every later chapter—descriptive statistics, comparison across load cases, outlier screening—operates on a table whose columns mean exactly one thing. Cleaning is not overhead; it is the contract that makes the pivot table's numbers defensible.

← Previous 📖 Contents Next →