PivotTables¶

CRITICAL: Required Parameters¶

pivotTableName is REQUIRED for almost all PivotTable operations across pivottable, pivottable_calc, and pivottable_field tools. The only exception is list (which lists all PivotTables). Always specify the PivotTable name.

Calculated Fields vs DAX Measures¶

PivotTable calculated fields work well for simple single-table formulas. Use DAX measures for complex scenarios.

Calculated Field Workflow¶

pivottable_calc(CreateCalculatedField, fieldName="Revenue", formula="=Quantity*UnitPrice")
pivottable_field(AddValueField, fieldName="Revenue", aggregationFunction="Sum")

DAX Measure Workflow (for complex scenarios)¶

table(add-to-data-model, tableName="Sales")
datamodel(create-measure, measureName="Revenue", daxFormula="SUMX(Sales, Sales[Quantity]*Sales[UnitPrice])")
pivottable(create-from-datamodel, ...)  # Measure automatically available

When to Use DAX Instead of Calculated Fields¶

• Multi-table calculations (need relationships between tables)
• Complex logic (time intelligence, YTD, running totals)
• Calculations involving filtered contexts
• Reusable measures across multiple PivotTables

PivotTable Source Types¶

Rule: If you need calculated revenue/aggregations, use Data Model as source.

Refresh Behavior (CRITICAL)¶

PivotTables do NOT auto-refresh when source data changes!

After adding rows to source table:

table(append, ...)           # Add rows to worksheet table
pivottable(refresh, ...)     # Refresh PivotTable to see new rows
datamodel(refresh)           # ALSO refresh Data Model if using DAX measures

After Power Query refresh:

powerquery(refresh, ...)     # Refreshes Power Query AND Data Model
## PivotTables connected to Data Model auto-refresh

PivotCache Options¶

• pivottable(get-cache-options): Read refresh, retained-item, optimization, and saved-source settings.
• pivottable(set-cache-options): Set enableRefresh, refreshOnFileOpen, missingItemsLimit, optimizeCache, or saveSourceData.
• missingItemsLimit values: Default, None, Max, Max2.
• Deleted-item retention applies only to regular PivotTables. OLAP/Data Model caches manage members in the model.

Field Configuration¶

Row/Column/Value Fields¶

When creating PivotTables, configure fields in order: 1. Add Row fields: pivottable_field(AddRowField, fieldName="Region") 2. Add Column fields: pivottable_field(AddColumnField, fieldName="Year")
3. Add Value fields: pivottable_field(AddValueField, fieldName="Amount", aggregationFunction="Sum") 4. Add filters: pivottable_field(AddFilterField, fieldName="Status") 5. Refresh to update display: pivottable(refresh, pivotTableName="...")

IMPORTANT: Field operations are structural only - they modify the PivotTable layout but don't trigger visual refresh. Call pivottable(refresh) after configuring all fields to update the display. This is especially important for OLAP/Data Model PivotTables.

Manual Grouping¶

pivottable_field(group-items, fieldName="Region", itemNames=["North", "South"], groupName="Core Regions")
## Use groupedFieldName from the result:
pivottable_field(ungroup-field, groupedFieldName="Region2")

Manual grouping requires a regular PivotTable and a field already placed in the Row or Column area. OLAP/Data Model PivotTables must add grouping columns in the model.

Drill Through¶

pivottable(drill-through, pivotTableName="SalesPivot", cellAddress="G4")

The target must be a value cell in a regular PivotTable data body. Excel creates a new worksheet containing the underlying source rows. OLAP/Data Model drill-through is provider-dependent and intentionally not exposed as a deterministic operation.

Aggregation Functions for Value Fields¶

Common Patterns¶

Revenue Analysis from Worksheet Table¶

## Option 1: Add revenue column to source table FIRST
range(set-formula, sheetName="Sales", rangeAddress="I2", formula="=[@Quantity]*[@UnitPrice]")
pivottable(create-from-table, sourceTableName="SalesTable", ...)
pivottable_field(AddValueField, fieldName="Revenue", aggregationFunction="Sum")  # Works!

## Option 2: Use Data Model (RECOMMENDED)
table(add-to-data-model, tableName="SalesTable")
datamodel(create-measure, measureName="Revenue", daxFormula="SUMX(SalesTable, SalesTable[Quantity]*SalesTable[UnitPrice])")
pivottable(create-from-datamodel, ...)  # Measure automatically available

Multi-Table Analysis¶

Always use Data Model for multi-table analysis:

table(add-to-data-model, tableName="Sales")
table(add-to-data-model, tableName="Products")
datamodel_relationship(create-relationship, fromTable="Sales", fromColumn="ProductID", toTable="Products", toColumn="ProductID")
datamodel(create-measure, tableName="Sales", measureName="Revenue", daxFormula="SUMX(Sales, RELATED(Products[Price])*Sales[Quantity])")
pivottable(create-from-datamodel)

Layout Styles¶

The layoutStyle parameter controls PivotTable appearance:

Common Errors and Solutions¶