2. Preparing CAE results for pivot analysis

A PivotTable is only as reliable as the table it reads from. CAE post-processors export results in layouts optimized for human reading — merged title blocks, multi-row headers, units embedded in labels, blank spacer rows, and repeated group headings — none of which a PivotTable can interpret. Before any aggregation or statistical analysis, the raw export must be converted into a flat table: one header row, one record per row, one variable per column, and no merged cells. This chapter covers the full preparation pipeline: importing solver output, cleaning headers, handling missing values, and normalizing the table structure.

What a Flat Table Requires

A flat (normalized) table satisfies four rules. First, the first row contains exactly one header per column, with no merged or stacked headers. Second, every subsequent row is a single observation — for CAE data, typically one element, node, load case, or time step. Third, each column holds one variable of a single type (all numeric, or all text), with units recorded in the header or a separate metadata column rather than mixed into values. Fourth, there are no subtotal rows, blank rows, or repeated group labels inside the data region.

A typical solver export violates all four. For example, an implicit static analysis may export a table where row 1 reads Displacement Results (mm), row 2 contains Node, UX, UY, UZ, Magnitude, and rows 3–5 are blank before data begins. A PivotTable built on this range will treat the title as a field name and the blank rows as null records. The fix is structural, not cosmetic.

Importing CAE Output

Most CAE tools export results as CSV, whitespace-delimited text, or XLSX. For CSV and text, use Data > Get Data > From Text/CSV rather than double-clicking the file, because the import dialog lets you set the delimiter, encoding, and decimal separator before the data lands in a worksheet. Solver exports frequently use fixed-width columns or tab delimiters; selecting the wrong delimiter collapses all fields into column A.

Two import settings matter most for CAE data:

  • Decimal and thousands separators. European-locale solvers may write 1,234.56 or 1.234,56. If the locale is misdetected, values import as text and every PivotTable aggregation returns a count instead of a sum. Verify by checking that a numeric column right-aligns after import.
  • Column data types. Force ID columns (Node ID, Element ID) to Text if they must never be summed, and force result columns to Decimal Number. Leaving everything as Any invites mixed types.

For large result sets — millions of element rows — import directly into the Data Model (check "Add this data to the Data Model" in the import dialog) rather than a worksheet. The Data Model handles row counts beyond the 1,048,576-row worksheet limit and keeps the workbook responsive.

Cleaning Headers

Header cleaning is where most preparation time is spent. The goal is a single header row with unique, machine-friendly names. Work through these steps in order:

  1. Delete title and unit rows above the header. Move the analysis name and units into a metadata block on a separate sheet, or append units to the header text (e.g., UZ_mm).
  2. Unmerge all cells in the header region (Home > Merge & Center > Unmerge Cells). Merged headers produce blank field names in the PivotTable field list.
  3. Fill down group headers. If the export uses a two-tier header such as Stress spanning SX, SY, SZ, flatten it to Stress_SX, Stress_SY, Stress_SZ.
  4. Remove special characters and spaces. Replace spaces, parentheses, and slashes with underscores. Field names containing [, ], or leading digits cause errors in calculated fields and DAX measures.
  5. Ensure uniqueness. Duplicate headers cause Excel to silently rename the second occurrence to Column1, which then appears in the field list and confuses later steps.
Keep a small "header map" sheet that records the original export label, the cleaned field name, the unit, and the physical quantity. When the solver export format changes between runs, the map makes re-cleaning a lookup rather than a re-derivation.

Handling Missing Values

CAE exports contain missing values for legitimate physical reasons: a node not included in a result set, an element with no stress recovery, a load case that did not converge, or a sensor channel that was inactive. Excel represents these as empty cells, #N/A, NaN, -999, or a text placeholder such as N/A. Each behaves differently in a PivotTable:

RepresentationPivotTable behaviorRecommended action
Empty cellExcluded from count and average; treated as blankAcceptable if the absence is meaningful; otherwise impute or flag
#N/A / NaNPropagates errors into calculated fieldsConvert to blank or a sentinel via IFERROR
-999 sentinelIncluded in averages and sums, corrupting statisticsReplace with blank using Find & Replace or a conditional column
Text N/AForces the column to text type; numeric aggregation failsReplace with blank, then re-type the column as numeric

The decision to impute, exclude, or flag missing values is a statistical one, not a formatting one. For a displacement field where a missing node simply was not requested, exclusion is correct. For a stress field where -999 marks elements that failed stress recovery, replacing with the mean would bias the result; instead add a Valid flag column (1/0) and filter the PivotTable on it, so the excluded population is visible and auditable.

=IF(OR(ISBLANK(D2),D2=-999,ISTEXT(D2)),"",D2)   ' clean value
=IF(OR(ISBLANK(D2),D2=-999,ISTEXT(D2)),0,1)      ' validity flag

Structuring the Flat Table

Once headers are clean and missing values are resolved, reshape the table so each row is one observation. CAE exports often arrive in wide form — one row per node with separate columns for each load case or time step (UZ_LC1, UZ_LC2, UZ_LC3). PivotTables prefer long form, where load case is a column value rather than a column name. Convert wide to long with Power Query: select the identifier columns (Node ID, coordinates), then Transform > Unpivot Columns. The result has columns NodeID, X, Y, Z, LoadCase, UZ_mm — a structure the PivotTable can slice by load case directly.

The diagram below summarizes the transformation from a raw solver export to a PivotTable-ready flat table, showing where each cleaning operation applies.

From Raw CAE Export to PivotTable-Ready Flat Table 1. Raw Solver Export Displacement Results (mm) Node | UX | UY | UZ | Mag (blank spacer row) 101 | 0.12 | -0.03 | 0.45 | 0.47 102 | -999 | 0.01 | 0.22 | N/A 103 | 0.08 | 0.02 | 0.31 | 0.32 merged header · sentinel · text NaN 2. Clean & Normalize • Delete title row, unmerge • Flatten headers → NodeID, UX_mm… • Replace -999 / N/A → blank • Add Valid flag (1/0) • Unpivot load-case columns • Force numeric types Power Query: From Text/CSV → Unpivot → Change Type → Close & Load 3. Flat Table (Pivot-Ready) NodeID | LoadCase | UZ_mm | Valid 101 | LC1 | 0.45 | 1 101 | LC2 | 0.51 | 1 102 | LC1 | (blank) | 0 103 | LC1 | 0.31 | 1 one row = one observation one header · one type per column no merged cells · no subtotals Pre-Pivot Validation Checklist Header integrity unique names, no blanks, no merged cells Type integrity numeric columns right-align; no text in result fields Missing-value policy sentinels removed; Valid flag populated Row granularity one observation per row; no subtotal/blank rows Cross-check: row count after unpivot = original rows × load cases; sum of Valid = expected valid observations If a PivotTable shows Count instead of Sum, a numeric column still contains text — return to type integrity. If field list shows Column1, duplicate headers survived cleaning.

Verification Before Building the PivotTable

Run four checks before inserting the PivotTable. First, confirm the header row has no blanks and no duplicates — select the header range and use COUNTBLANK and a duplicate check. Second, confirm numeric columns right-align; a left-aligned result column means text values survived. Third, confirm the row count matches expectation: after unpivoting three load cases from 10,000 nodes, the flat table should contain 30,000 data rows. Fourth, confirm the Valid flag sum equals the number of observations you expect to analyze.

Two failure symptoms are diagnostic. If a PivotTable returns counts where sums are expected, a numeric column contains text — re-check the import decimal separator and the missing-value replacement. If the field list shows Column1 or Column2, duplicate or blank headers survived cleaning. Both are structural defects in the source table, not PivotTable settings, and must be fixed at the data layer.

Do not "fix" a PivotTable by dragging fields around when the underlying table is malformed. A PivotTable silently ignores blank rows and coerces text to counts; the resulting numbers look plausible but are wrong. Always validate the flat table first, then build the PivotTable on a verified range or Data Model table.

With a clean, flat, type-consistent table in place — headers unique, missing values resolved and flagged, load cases unpivoted into a single column — the data is ready for the aggregation, grouping, and statistical analysis that follow. The preparation effort invested here determines whether every subsequent PivotTable result can be trusted.

← Previous 📖 Contents Next →