Table.Transpose
Swaps rows and columns: each row becomes a column and each column a row.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.Transpose(table as table, optional columns as any) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table to swap. |
columns | any optional | Names for the new columns, as many as the table has rows; Column1, Column2 ... when left out. |
Returns
table. A row per old column, a column per old row.
Remarks
- The old column names are not carried over. To keep them, turn them into a row first with
Table.DemoteHeaders; to name the new columns from the first column, promote after transposing.
Examples
Default names
Table.Transpose(#table({"Shop", "Jan", "Feb"}, {{"Oslo", 10, 12}, {"Bergen", 7, 8}}))| Column1 | Column2 |
|---|---|
| Oslo | Bergen |
| 10 | 7 |
| 12 | 8 |
Names of your own
Table.Transpose(#table({"Shop", "Jan", "Feb"}, {{"Oslo", 10, 12}, {"Bergen", 7, 8}}), {"Oslo", "Bergen"})| Oslo | Bergen |
|---|---|
| Oslo | Bergen |
| 10 | 7 |
| 12 | 8 |
Shops as columns, months as rows
let
Sales = #table({"Shop", "Jan", "Feb"}, {{"Oslo", 10, 12}, {"Bergen", 7, 8}})
in
Table.PromoteHeaders(Table.Transpose(Table.DemoteHeaders(Sales)))| Shop | Oslo | Bergen |
|---|---|---|
| Jan | 10 | 7 |
| Feb | 12 | 8 |
The old header row becomes the first column, and the shops name the new columns.
Related functions
Table.DemoteHeaders, Table.PromoteHeaders, Table.Pivot, Table.UnpivotOtherColumns.