UNPIVOT enhancements: multi-level headers, filter predicate, column-major ordering

[jimmytacks]

Background

UNPIVOT (added in #221 / PR #222) reshapes a single-header-row, single-header-column crosstab into a tidy 3-column [RowHeading, ColHeading, Value] table. It currently supports:

• range — the crosstab
• dropBlanks — omit empty value rows
• headers — TRUE prepends a default Row, Column, Value row, or pass a 3-element array of custom column names

These follow-up ideas (from the original Notion proposal, inspired by Excel-native PIVOTBY) were deliberately deferred from the base version to keep it focused.

Proposed enhancements

1. Multi-level headers (headerDepth / indexDepth)

PIVOTBY groups by several row/column fields at once. The inverse: support crosstabs with N header rows and/or M index columns (e.g. Region→Country down the side, Year→Quarter across the top). Add headerDepth and indexDepth params; the output gains one column per header level instead of just two key columns (so output width becomes indexDepth + headerDepth + 1).

2. Filtering (filter_array-style)

Accept an optional predicate to keep only certain unpivoted rows (e.g. value > 0, or colKey in a set), saving the caller from wrapping the result in FILTER.

3. Value-position / ordering flexibility

PIVOTBY auto-detects header orientation. UNPIVOT could accept a flag for crosstabs whose value block should walk column-major rather than row-major, to control output ordering.

Notes

• These should compose with the existing dropBlanks / headers args without breaking the current scalar signature.
• Multi-level support (Lambda Boss — ExcelDNA add-in for LAMBDA library management #1) is the highest-value next step per the original proposal; Tracer bullet: add-in skeleton + popup + Name Manager injection #2 and LAMBDA file parsing and prefix rewriting #3 are conveniences.
• Relates to LAMBDA idea: UNPIVOT (fast) #221.