Table of contents
TOC
Collapse the table of content
Expand the table of content

Table.Pivot

Owen Duncan|Last Updated: 10/5/2018
|
1 Contributor

About

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  

Arguments

ArgumentDescription
tableThe Table to modify.
pivotValuesThe values to transform.
attributeColumnThe column to make the attribute.
valueColumnThe column to make the value.
optional aggregationFunctionFunction to aggregate values.

Examples

// 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")  
keyattribute1attribute2attribute3
key11null3
key224null
// 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)  
keyattribute1attribute2attribute3
key11nullnull
key224null
© 2018 Microsoft