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  

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.
// 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
Show: