Table.Pivot
This topic applies to the Power Query Formula Language which can be used with Power Query and Power BI Desktop to build queries that mashup data. See the list of function categories.
Given a table and attribute column containing pivotValues, creates new columns for each of the pivot values and assigns them values from the valueColumn. An optional aggregationFunction can be provided to handle multiple occurrence of the same key value in the attribute column.
Table.Pivot(table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table
| Argument | Description |
|---|---|
| table | The Table to modify. |
| pivotValues | The values to transform. |
| attributeColumn | The column to make the attribute. |
| valueColumn | The column to make the value. |
| optional aggregationFunction | Function to aggregate values. |
// Simple input with no key + attribute conflicts. In other words, (key,attribute) is unique.
Table.Pivot(
Table.FromRecords({
[ key = "key1", attribute = "attribute1", value = 1 ],
[ key = "key1", attribute = "attribute3", value = 3 ],
[ key = "key2", attribute = "attribute1", value = 2 ],
[ key = "key2", attribute = "attribute2", value = 4 ]
}), { "attribute1", "attribute2", "attribute3" }, "attribute", "value")
| key | attribute1 | attribute2 | attribute3 |
|---|---|---|---|
| key1 | 1 | null | 3 |
| key2 | 2 | 4 | null |
// Same input as Example 2, but with an additional function specified to resolve the conflict – in this case, to take the minimum value. Note that this resolution method is the same as the PIVOT clause in SQL Server and most other DBMS’s.
Table.Pivot(
Table.FromRecords({
[ key = "key1", attribute = "attribute1" , value = 1 ],
[ key = "key1", attribute = "attribute3" , value = 3 ],
[ key = "key2" , attribute = "attribute1" , value = 2 ],
[ key = "key2", attribute = "attribute1", value = 8 ],
[ key = "key2", attribute = "attribute2", value = 4 ]
}), { "attribute1", "attribute2", "attribute3" }, "attribute", "value", List.Min)
| key | attribute1 | attribute2 | attribute3 |
|---|---|---|---|
| key1 | 1 | null | null |
| key2 | 2 | 4 | null |
Show: