Table.Unpivot Function in Power Query

The Table.Unpivot function in Power Query given a list of table columns, transforms those columns into attribute-value pairs.

Syntax

Table.Unpivot(
    table as table,
    columnsToUnpivot as list,
    attributeColumn as text,
    valueColumn as text
) as table

The function has the following parameters:

table: The original table you want to unpivot.
columnsToUnpivot: A list of column names to convert into row values.
attributeColumn: The name of the new column that will hold the former column names.
valueColumn: The name of the new column that will hold the former column values.

Example: Create a table from records.

Power Query M

let
    Source = Table.FromRecords({
        [Year = "2023", Jan = 100, Feb = 120, Mar = 110],
        [Year = "2024", Jan = 130, Feb = 140, Mar = 150],
        [Year = "2025", Jan = 160, Feb = 170, Mar = 180],
        [Year = "2026", Jan = 190, Feb = 200, Mar = 210]
    })
in
    Source

The output of the above code is shown below:

Example: Unpivot selected columns.

Power Query M

let
    Source = Table.FromRecords({
        [Year = "2023", Jan = 100, Feb = 120, Mar = 110],
        [Year = "2024", Jan = 130, Feb = 140, Mar = 150],
        [Year = "2025", Jan = 160, Feb = 170, Mar = 180],
        [Year = "2026", Jan = 190, Feb = 200, Mar = 210]
    }),

    Unpivoted = Table.Unpivot(Source, {"Jan", "Feb", "Mar"}, "Month", "Sales")
in
    Unpivoted

The output of the above code is shown below:

Previous Next