Transformations
Pivot Table
Summarises rows into a pivot table, the way Excel does: fields down the side, fields across the top, and a summary of one column in every cell.
When to use it
Use it to turn a long list into a cross-tab — sales per region per quarter, headcount per department per grade — ready for a report or a spreadsheet.
In a pipeline
File InputPivot TableExport Excel
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| Rows | select | — | No row fields |
| Columns | select | — | No column fields |
| Values | select | Yes | Count of rows |
| Summarise By | select | — | sum |
| Subtotal each outer row group | checkbox | — | true |
| Grand total row | checkbox | — | true |
| Grand total column | checkbox | — | true |
Example
Sales by region and quarter
- Rows=Region
- Columns=Quarter
- Values=Amount
- Summarise By=Sum
| Region | Quarter | Amount |
|---|---|---|
| North | Q1 | 900 |
| North | Q2 | 500 |
| South | Q1 | 700 |
| South | Q1 | 300 |
| Region | Q1 | Q2 | Grand Total |
|---|---|---|---|
| North | 900 | 500 | 1400 |
| South | 1000 | — | 1000 |
| Grand Total | 1900 | 500 | 2400 |
A cell with nothing behind it is left empty, exactly as Excel leaves it.
Worth knowing
- Summarise By offers Excel's own functions: Sum, Count, Count Numbers, Distinct Count, Average, Max, Min, Product, StdDev, StdDevp, Var and Varp. StdDev and Var are the sample forms and need at least two numbers, as in Excel.
- Count counts filled cells, so it is the one summary that works without a Values column — leave Values empty to count rows.
- Rows and Columns each take several fields. With more than one Rows field, each outer group gets its own subtotal row ("North Total"), which the Subtotal switch turns off.
- With no Columns fields the table has a single value column named as Excel names it, such as "Sum of Amount".
- Labels sort ascending, numbers as numbers, and an empty value reads "(blank)".
- The columns across the top come from the data, so the editor only knows them once the node has run; preview it before picking columns in the next node.