Table.Pivot Function in Power Query

The Table.Pivot function in Power Query is used to rotate a column's unique values into separate columns—each acting like a field in a new table layout.

Syntax

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

The function has the following parameters:

table: Your input table
pivotValues: List of values in attributeColumn that should become column headers
attributeColumn: Column containing the values you want to pivot into headers
valueColumn: Column whose values we want under the new pivoted columns
aggregationFunction: It is an optional parameter. Used if multiple values exist for the same combination (like List.Max, List.Min, List.Sum)

Example: Sum of Sales by Month

Let’s say we have sales data by region and month:

Input

Region	Month	Sales
East	Jan	100
East	Feb	120
West	Jan	90
West	Jan	60
West	Feb	110

We want months ("Jan", "Feb") to become columns, with the sum of sales per region.

Power Query M

Table.Pivot(
    Table.FromRecords({
        [Region = "East", Month = "Jan", Sales = 100],
        [Region = "East", Month = "Feb", Sales = 120],
        [Region = "West", Month = "Jan", Sales = 90],
        [Region = "West", Month = "Jan", Sales = 60],
        [Region = "West", Month = "Feb", Sales = 110]
    }),
    {"Jan", "Feb"},
    "Month",
    "Sales",
    List.Sum
)

The output of the above code is shown below:

Region	Jan	Feb
East	100	120
West	150	110

Example: Employee Attendance Tracker

Input Table

Employee	Date	Status
Alice	01-Apr-25	Present
Alice	02-Apr-25	Absent
Bob	01-Apr-25	Present
Bob	02-Apr-25	Present

Goal

Pivot Date into columns to show attendance status per employee.

Power Query M

Table.Pivot(
    Table.FromRecords({
        [Employee = "Alice", Date = "01-Apr-25", Status = "Present"],
        [Employee = "Alice", Date = "02-Apr-25", Status = "Absent"],
        [Employee = "Bob", Date = "01-Apr-25", Status = "Present"],
        [Employee = "Bob", Date = "02-Apr-25", Status = "Present"]
    }),
    {"01-Apr-25", "02-Apr-25"},
    "Date",
    "Status"
)

The output of the above code is shown below:

Employee	01-Apr-25	02-Apr-25
Alice	Present	Absent
Bob	Present	Present

Example: Departmental Expenses by Category

Input Table

Dept	Category	Amount
HR	Travel	2000
HR	Supplies	500
Finance	Travel	3000
Finance	Software	700
HR	Travel	1500

Goal

Pivot Category into columns, aggregate Amount using List.Sum.

Power Query M

Table.Pivot(
    Table.FromRecords({
        [Dept = "HR", Category = "Travel", Amount = 2000],
        [Dept = "HR", Category = "Supplies", Amount = 500],
        [Dept = "Finance", Category = "Travel", Amount = 3000],
        [Dept = "Finance", Category = "Software", Amount = 700],
        [Dept = "HR", Category = "Travel", Amount = 1500]
    }),
    {"Travel", "Supplies", "Software"},
    "Category",
    "Amount",
    List.Sum
)

The output of the above code is shown below:

Dept	Travel	Supplies	Software
HR	3500	500	null
Finance	3000	null	700

Tips for Using Table.Pivot

We want to transform rows into columns based on a value.
Always check for duplicates — If there are multiple rows with the same key-attribute pair, we will need an aggregation function (like List.Sum, List.Max, etc.).
Use Table.Distinct before Table.Pivot if you're sure there should be only one value per key-attribute.
Make sure your pivotValues list includes all possible values — if missing, some columns won’t appear.

Previous Next