Table.Transpose
Swaps rows and columns, so each column becomes a row and each row becomes a column.
Table.Transpose(table as table, optional columns as any) as tableReturns: A table with its rows and columns swapped.
What it does
Table.Transpose flips a table on its side. A chart of accounts laid out with one account per column becomes one account per row. It is a simple flip, not a summary. For a grid of months to a long list, Table.Unpivot is the more common choice.
Syntax and arguments
- table: the table to flip. Required.
- columns: optional. Limits the transposition to the columns you name, for advanced cases. Leave it out for the usual flip.
Example
An account list was exported with each account as a column, and the account names in a header row. The table is flipped so each account is a row, and the first row becomes the headers.
let
Source = Excel.CurrentWorkbook(){[Name = "AccountsAcross"]}[Content],
Flipped = Table.Transpose(Source),
Named = Table.PromoteHeaders(Flipped, [PromoteAllScalars = true])
in
Named
After the flip, each original column is now a row. The first row of the flipped table holds the original row labels, so promoting it gives the new table its headings.
Things to watch
- Transposing puts every value in the same type. Mixed text and numbers can become text, so set the types afterwards.
- Check the result carefully. The first row of a transposed table holds labels that you need to read before you promote them.
- For monthly grids, use Table.Unpivot or Table.UnpivotOtherColumns. They are built for that job.
- Query folding: transposing does not fold. Power Query flips the loaded data.
In business terms
Reports that list accounts across the top are common in older schedules. Flipping them in the query puts the data into the one-row-per-account layout that summaries and lookups need.