Shape · Summarize and reshape

Unpivot

Turns chosen columns into rows of name and value pairs. For example, Jan, Feb and Mar columns become month and sales rows.

Also known as: Melt, wide to long, transpose

How it works

Choose the Columns to unpivot; every column you do not unpivot is kept on each output row, so the Keep as rows (id columns) picker does not change the output. Each chosen column becomes one row per input row, with the column's name in the Attribute column name (name by default) and its cell in the Value column name (value by default). Null cells are kept as rows, not dropped. Columns of one type keep it; mixing numbers and text in the melted columns gives text. Rows come out in input order, one block of pairs per input row. Pivot does the reverse.

Inputs and outputs

Settings

{
  idColumns: string[]
  valueColumns: string[]
  nameColumn: string
  valueColumn: string
}

Example

A sheet has one column per month. Keep store as rows, unpivot Jan, Feb and Mar, and name the attribute column month and the value column sales.

More in Summarize and reshape

• Aggregate — Collapses rows into one row per group and works out values for each: sum, count, average, median, mode, percentiles, longest text and more, cash-flow values such as NPV or IRR, and for a geometry the dissolved shape, extent or centroid. With no group, you get one grand-total row.
• Group Aggregate — Works out a group total and writes it onto every row instead of collapsing them, as the value, a ratio or a percent. For example, each sale as a share of its region's total.
• Running Aggregations — Adds running totals that build up row by row: sum, average, median, min, max, count or standard deviation, optionally restarting per group. For example, year-to-date sales.
• Pivot — Turns the values of one column into new columns, such as one row per store with a column per month. The reverse of Unpivot.
• Transpose — Swaps rows and columns, so every column becomes a row. Best for small, wide summary tables.
• Split to Rows — Turns a cell holding several delimited values into one row per value. For example, 'red;green;blue' becomes three rows.
• Split to Columns — Breaks one text column into several columns at a delimiter, such as 'London, UK' into city and country.
• Explode — Turns each item of a list column into its own row. For comma-separated text, use Split to Rows instead.