Table.Transpose Function in Power Query M (3 Easy Examples)

If you want to flip a table so its rows become columns and its columns become rows, Table.Transpose does exactly that. It swaps the two axes of your data in one step.

Table of Contents

Syntax of Table.Transpose Function

Table.Transpose(table as table, optional columns as any) as table

where

table (required, table). The table whose rows and columns you want to swap.
columns (optional, any). Supplies the output column names. Pass a list like {"RowA","RowB"}, or omit it to get Column1, Column2, and so on.

Returns: a table with rows and columns swapped. Existing headers are dropped to Column1, Column2, … unless columns is supplied.

The function works on the data only. It ignores your current header row, so those labels do not survive a plain transpose. You get generic Column1, Column2, … names instead. More on that below.

Example 1 – Flip a table with Table.Transpose

Say you have a small KPI table and you want quarters down the side instead of across the top. A plain Table.Transpose swaps the axes for you.

Here is the starting data:

Metric	Q1	Q2
Revenue	120	150
Cost	80	90

let
    Source = Excel.CurrentWorkbook(){[Name="Kpi"]}[Content],
    Transposed = Table.Transpose(Source)
in
    Transposed

This returns the following table:

Column1	Column2
Revenue	Cost
120	80
150	90

The axes are swapped, but notice the headers. The original Metric, Q1, and Q2 labels are gone, and the output columns are named Column1 and Column2.

Example 2 – Keep your labels through the flip

A plain transpose throws away your header labels. To carry them through, demote the headers into the data first, transpose, then promote the new first row back to headers.

Here is the starting data:

Metric	Q1	Q2
Revenue	120	150
Cost	80	90

let
    Source = Excel.CurrentWorkbook(){[Name="KpiHdr"]}[Content],
    Demoted = Table.DemoteHeaders(Source),
    Transposed = Table.Transpose(Demoted),
    Promoted = Table.PromoteHeaders(Transposed,[PromoteAllScalars=true])
in
    Promoted

This returns the following table:

Metric	Revenue	Cost
Q1	120	80
Q2	150	90

The [PromoteAllScalars=true] option lets non-text values become header names too. Now the labels survive the flip and the quarters sit in their own rows.

Example 3 – Name the output columns with the columns argument

If you already know what the new columns should be called, pass a list of names as the second argument. That skips the generic Column1, Column2 names.

Here is the starting data:

Attribute	Material	Color
Weight	Steel	Black
Height	Aluminum	Silver

let
    Source = Excel.CurrentWorkbook(){[Name="Item"]}[Content],
    Transposed = Table.Transpose(Source,{"RowA","RowB"})
in
    Transposed

This returns the following table:

RowA	RowB
Weight	Height
Steel	Aluminum
Black	Silver

The two output columns are named RowA and RowB directly. The original headers are still dropped, but you control the new names instead of getting Column1 and Column2.

Things to keep in mind with Table.Transpose

A plain transpose drops your header labels entirely. They are not moved into a row, they are discarded, and the new columns become Column1, Column2, and so on.
To keep those labels, use the Table.DemoteHeaders -> Table.Transpose -> Table.PromoteHeaders pattern, and pass [PromoteAllScalars=true] so numeric first-row values still become header names.
Transposing mixes values from different source columns into one new column. When text and numbers land together, the column type can collapse to any and numbers may render as text. If you only need certain columns first, trim them with Table.SelectColumns before the flip.
This is not a pivot. Table.Transpose only swaps axes, with no key, no grouping, and no aggregation. Use Table.Pivot when you need to summarize values.
If you pass a number as columns, it must match the count of columns the transpose produces, otherwise the step errors.

Common questions about Table.Transpose

Why did my headers turn into Column1, Column2?

A plain Table.Transpose works on the data only and ignores the header row. Those labels are dropped, so the output gets generic names. Use the demote-transpose-promote pattern to keep them.

Is Table.Transpose the same as Table.Pivot?

No. Table.Transpose is a pure axis swap with no grouping or aggregation. Table.Pivot turns distinct values of a column into new columns and aggregates a value column, which is a different operation.

List of All Power Query Functions

Related Power Query Functions / Articles: