2. Preparing CAE results for pivot analysis

A PivotTable is only as reliable as the table it reads from. CAE post-processors rarely export data in a pivot-ready form: they emit multi-row headers, merged title cells, unit annotations, repeated block labels, and blank rows that separate load cases. If you connect a PivotTable directly to such an export, Excel will either refuse the range or silently misinterpret columns. This chapter covers the transformation from a raw solver or post-processor export into a single flat table — one header row, one record per observation, no merged cells, no embedded totals — that PivotTables can aggregate correctly.

What "flat" actually means for CAE data

A flat table satisfies four structural rules. First, exactly one header row sits at the top, and every header is a unique, non-empty text label. Second, each row represents one observation at the finest granularity you intend to analyze — typically one element, one node, one probe point, or one time/frequency step. Third, every attribute of that observation lives in its own column as a value, not as part of a label. Fourth, no blank rows, no subtotal rows, and no merged cells exist anywhere inside the data region.

The third rule is where CAE exports most often fail. A typical modal export might produce a column headed Mode 1 | 23.47 Hz, then Mode 2 | 61.02 Hz, and so on. That layout is a wide or cross-tab format: the mode number and frequency are fused into the header, so the PivotTable sees dozens of unrelated value columns instead of one Frequency_Hz column with a Mode identifier. The fix is to unpivot — stack those columns into rows so that each row carries Mode, Frequency_Hz, and the response quantity as separate fields.

Importing without losing fidelity

Prefer Data > Get Data > From Text/CSV over double-clicking the file. The Power Query preview lets you set the delimiter, the file origin (use UTF-8 for exports containing degree signs or Greek symbols), and the data types before anything lands in a worksheet. Two settings matter most for CAE files:

• Data Type Detection: set to Do Not Detect Types on first load. Solver exports frequently use 1.0E-03, 1,0E-03, or D exponents; automatic detection can coerce a displacement column to text or truncate precision.
• Column headers: if the export begins with title lines such as Job: bracket_v3 / Solver: Abaqus 2023, choose Use First Row as Headers only after removing those lines with Remove Top Rows.

For fixed-width exports (common from Nastran .pch and ANSYS tabular printouts), use From Text/CSV with Fixed Width, then verify column boundaries in the preview against a known line of the file. A one-character misalignment will shift every numeric field and produce plausible but wrong aggregates — always cross-check the first and last record against the raw file.

Cleaning headers

Headers must be unique, stable, and free of characters that break DAX or structured references. Apply these transformations in Power Query rather than by hand:

1. Trim and clean: Transform > Format > Trim removes leading/trailing spaces that make Stress_MPa and Stress_MPa  appear as two different fields.
2. Promote and rename: replace unit-bearing headers such as U1 (mm) with U1_mm. Keeping units in the name is good practice; keeping parentheses and spaces is not.
3. Deduplicate: repeated headers from multi-block exports (two columns both named Value) must be renamed to Value_1, Value_2 or, better, unpivoted so the block identity becomes a data column.
4. Remove embedded totals: delete any row whose first cell reads Total, Sum, or Average. PivotTables compute their own totals; a pre-aggregated row will be double-counted.

Handling missing values

CAE exports encode "no result" in several ways: empty cells, N/A, NaN, ******** (field overflow in fixed-width output), or -1 as a sentinel. Each needs a deliberate decision, because Excel's default behavior differs by type — an empty numeric cell becomes blank and is excluded from AVERAGE but counted as zero by some aggregation paths, while the text N/A forces the whole column to text.

In Power Query, select the affected column, use Replace Values to map each token to null, then set the column type to Decimal Number. Add an explicit Status column (OK / Missing / Diverged) so that later filtering is transparent rather than implicit. Never fill missing stress values with zero — a zero stress is a physical claim, not an absence of data.

Structuring the flat table

The target schema should separate dimensions (what was measured, where, under which condition) from measures (the numeric results). A workable schema for a structural study looks like this:

LoadCase | Step | Mode | Part | NodeID | X_mm | Y_mm | Z_mm | U_mag_mm | S_vm_MPa | Status

Each row is one node at one step of one load case. With this layout, a PivotTable can place LoadCase on Rows, Step on Columns, and Max of S_vm_MPa on Values to produce a load-case-by-step peak stress matrix in seconds — and the same source supports filtering by Part or plotting against Z_mm without re-importing.

Verification before you pivot

Before building the PivotTable, run four checks on the loaded table. Confirm the row count matches the expected number of records (nodes × steps × load cases, minus any deliberately excluded rows). Confirm that every measure column shows a numeric type in the Power Query header icon, not ABC. Confirm that the distinct count of LoadCase equals the number of load cases in the study. Finally, spot-check one node's values against the raw export. If a measure column has silently become text, the PivotTable will show a count instead of a sum — a symptom that is easy to miss because the table still renders.

Once the table is flat, typed, and verified, load it to the Data Model with Close & Load > Load To > Table (or Only Create Connection plus Add to Data Model for larger datasets). From that point on, refreshing the query re-runs the entire cleaning pipeline, so a new solver run can be dropped into the same folder and analyzed without repeating any manual steps.