Table.Pivot
Turns the values of one column into new columns, filled from another column: the opposite of unpivoting.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table in long form: a row per key, attribute and value. |
pivotValues | list | The attribute values that become columns, in the order the columns should come. |
attributeColumn | text | The column whose values name the new columns. |
valueColumn | text | The column whose values fill them. |
aggregationFunction | nullable function optional | A function that makes one value of the list of values for a cell, such as List.Sum. |
Returns
table. A row per combination of the other columns, then a column per pivot value.
Remarks
- The other columns (all but the attribute and value columns) decide the rows: one row per combination of their values, in the order they first appear.
- Without an aggregation function, a cell that gets two or more values is an error, and a cell that gets none is null. Give
List.Sum,List.Maxor another function of a list when rows can repeat. - Attribute values missing from pivotValues are left out. Transform data writes
List.Distinct(table[attributeColumn])so that every value gets a column.
Examples
Months as columns
let
Sales = #table({"Shop", "Month", "Sales"}, {{"Oslo", "Jan", 10}, {"Oslo", "Feb", 12}, {"Bergen", "Jan", 7}})
in
Table.Pivot(Sales, List.Distinct(Sales[Month]), "Month", "Sales")| Shop | Jan | Feb |
|---|---|---|
| Oslo | 10 | 12 |
| Bergen | 7 | null |
Bergen sold nothing in February: null.
Adding up repeated rows
Oslo has two January rows; List.Sum makes one value of them.
let
Sales = #table({"Shop", "Month", "Sales"}, {{"Oslo", "Jan", 10}, {"Oslo", "Feb", 12}, {"Bergen", "Jan", 7}, {"Oslo", "Jan", 5}, {"Bergen", "Feb", 9}})
in
Table.Pivot(Sales, {"Jan", "Feb"}, "Month", "Sales", List.Sum)| Shop | Jan | Feb |
|---|---|---|
| Oslo | 15 | 12 |
| Bergen | 7 | 9 |
Related functions
Table.Unpivot, Table.UnpivotOtherColumns, Table.Group, List.Distinct.