Table.Pivot

Table.Pivot is a Power Query M function that rotates data in a pair of columns representing attribute-value pairs into column headings. The function returns a pivoted table with new column headings based on the attribute column.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.Pivot(
   table as table,
   pivotValues as list,
   attributeColumn as text,
   valueColumn as text,
   optional aggregationFunction as nullable function,
) as table

Description

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

Examples

Imagine a Source table with the following records.

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]
   } )

You can use Table.Pivot to move the unique values in the Attribute column into their own column.

Table.Pivot( 
    Source,
    { "a", "b", "c" }, // These columns are created
    "attribute",
    "value"           
 )

The above example works great because each Key has only a single Attribute associated with it. This means Key X associates with a single Attribute of “A” and “C”, whereas Key Y associates with a single value of “A” and “B”. In case a key associated with multiple values, Table.Pivot allows you to perform an aggregation. For numbers you can for example return the Maximum Value associated with that key like:

Table.Pivot( 
    Source,
    { "a", "b", "c" },
    "attribute",
    "value",
    List.Max            // Aggregation performed on underlying values
 )

By default, the UI offers only a few aggregations—for example a distinct count, count, sum, max, min etc. If you’re working with text values, you can manually change the code to use the Text.Combine function.

Imagine working with below source table:

Table.FromRecords(
   {
      [ID = 1, Data = "Jupiter", Category = "Planet"],
      [ID = 1, Data = "Volga",   Category = "River" ],
      [ID = 2, Data = "Venus",   Category = "Planet"],
      [ID = 1, Data = "Nile",    Category = "River" ],
      [ID = 2, Data = "Nile",    Category = "River" ],
      [ID = 2, Data = "Earth",   Category = "Planet"]
   } )

You want to take all distinct values of the Category Column and pivot them into their own column. As aggregation, use the Text.Combine function to create a comma-separated list of text values.

Table.Pivot(
   Source, 
   List.Distinct( Source[Category] ), 
   "Category", 
   "Data", 
   each Text.Combine( _, ", " ) // Custom aggregation, not in UI
)

/* --------------------------------------------------
 -- One can also manually provide the values to pivot
 ---------------------------------------------------- */

Table.Pivot(
   Source, 
   { "Planet", "River" }, 
   "Category", 
   "Data", 
   each Text.Combine( _, ", " )
)

Related articles

Learn more about Table.Pivot in the following articles:

• Pivot Text Values in Power Query – Explained
  What is the Pivot Columns operation in Power Query? And how can you use it to pivot text values and numbers? Follow along to find out! » Read more

Related functions

Other functions related to Table.Pivot are:

• Table.Unpivot
• Table.UnpivotOtherColumns

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-pivot