Downloads
Power Query MTable: headers and shapeIntermediate

Table.Pivot

Turns the values of one column into new column headers, filled with the values from another column.

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

Returns: A table with one column for each pivot value.

What it does

Table.Pivot takes a long list, where each row holds one month and one value, and spreads the months across the top as columns. It produces the grid that a reader expects to see. Every column that is not used for the pivot becomes part of the row key, so the table should be cut down to the key columns first.

Syntax and arguments

Example

A general ledger has been summarized into one row per account and month. The months are spread across the top, with the net amount in each cell.

let
    Source = Excel.CurrentWorkbook(){[Name = "NetByMonth"]}[Content],
    Slim = Table.SelectColumns(Source, {"Account", "Month", "Net"}),
    Grid = Table.Pivot(Slim, List.Distinct(Slim[Month]), "Month", "Net", List.Sum)
in
    Grid

The Account column is the row key. Each distinct month becomes a column, and List.Sum adds up any amounts that share an account and a month.

Things to watch

In business terms

Budget versus actual grids, trial balances by period and sales by month are all pivoted layouts. Building them in the query gives a single, repeatable table that matches the report format.

Related functions

All Power Query M functions · View as Markdown · M basics