Power Query
Search…
published
Introduction
Accessing Data
Action
Binary
Binary Formats
Combiner
Comparer
Cube
Date
Date Time
Date Time Zone
Diagnostics
Duration
Error
Expression
Function
Lines
List
Logical
Metadata
Number
Record
Replacer
Splitter
Table
ItemExpression.From
RowExpression.Column
RowExpression.From
Table.AddColumn
Table.AddIndexColumn
Table.AddJoinColumn
Table.AddKey
Table.AggregateTableColumn
Table.AlternateRows
Table.Buffer
Table.Column
Table.ColumnCount
Table.ColumnNames
Table.ColumnsOfType
Table.Combine
Table.CombineColumns
Table.Contains
Table.ContainsAll
Table.ContainsAny
Table.DemoteHeaders
Table.Distinct
Table.DuplicateColumn
Table.ExpandListColumn
Table.ExpandRecordColumn
Table.ExpandTableColumn
Table.FillDown
Table.FillUp
Table.FilterWithDataTable
Table.FindText
Table.First
Table.FirstN
Table.FirstValue
Table.FromColumns
Table.FromList
Table.FromPartitions
Table.FromRecords
Table.FromRows
Table.FromValue
Table.FuzzyJoin
Table.FuzzyNestedJoin
Table.Group
Table.HasColumns
Table.InsertRows
Table.IsDistinct
Table.IsEmpty
Table.Join
Table.Keys
Table.Last
Table.LastN
Table.MatchesAllRows
Table.MatchesAnyRows
Table.Max
Table.MaxN
Table.Min
Table.MinN
Table.NestedJoin
Table.Partition
Table.Pivot
Table.PositionOf
Table.PositionOfAny
Table.PrefixColumns
Table.Profile
Table.PromoteHeaders
Table.Range
Table.RemoveColumns
Table.RemoveFirstN
Table.RemoveLastN
Table.RemoveMatchingRows
Table.RemoveRows
Table.RemoveRowsWithErrors
Table.RenameColumns
Table.ReorderColumns
Table.Repeat
Table.ReplaceErrorValues
Table.ReplaceKeys
Table.ReplaceMatchingRows
Table.ReplaceRelationshipIdentity
Table.ReplaceRows
Table.ReplaceValue
Table.ReverseRows
Table.RowCount
Table.Schema
Table.SelectColumns
Table.SelectRows
Table.SelectRowsWithErrors
Table.SingleRow
Table.Skip
Table.Sort
Table.Split
Table.SplitColumn
Table.ToColumns
Table.ToList
Table.ToRecords
Table.ToRows
Table.TransformColumnNames
Table.TransformColumns
Table.TransformColumnTypes
Table.TransformRows
Table.Transpose
Table.Unpivot
Table.UnpivotOtherColumns
Table.View
Table.ViewFunction
Tables.GetRelationships
Text
Time
Type
Uri
Values
Powered By GitBook
Table.Pivot

Table.Pivot

Given a pair of columns representing attribute-value pairs, rotates the data in the attribute column into a column headings.
function (table as table, pivotValues as list, attributeColumn as text, valueColumn as text, optional aggregationFunction as nullable function) as table

Description

Given a pair of columns representing attribute-value pairs, rotates the data in the attribute column into a column headings.

Category

Table.Column operations

Examples

Take the values "a", "b", and "c" in the attribute column of table ({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] }) and pivot them into their own column.
1
Table.Pivot(Table.FromRecords({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] }), { "a", "b", "c" }, "attribute", "value")
Copied!
Table.FromRecords({[ key = "x", a = 1, b = null, c = 3 ], [ key = "y", a = 2, b = 4, c = null ]})
Take the values "a", "b", and "c" in the attribute column of table ({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "x", attribute = "c", value = 5 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] }) and pivot them into their own column. The attribute "c" for key "x" has multiple values associated with it, so use the function List.Max to resolve the conflict.
1
Table.Pivot(Table.FromRecords({ [ key = "x", attribute = "a", value = 1 ], [ key = "x", attribute = "c", value = 3 ], [ key = "x", attribute = "c", value = 5 ], [ key = "y", attribute = "a", value = 2 ], [ key = "y", attribute = "b", value = 4 ] }), { "a", "b", "c" }, "attribute", "value", List.Max)
Copied!
Table.FromRecords({[ key = "x", a = 1, b = null, c = 5 ], [ key = "y", a = 2, b = 4, c = null ]})
Previous
Table.Partition
Next
Table.PositionOf
Last modified 3yr ago
Copy link
Contents
Table.Pivot
Description
Category
Examples