Table.Transpose - Table Function

Table.Transpose is a Power Query M function that makes columns into rows and rows into columns. The function returns a table with the rows and columns transposed.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

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

Argument	Attribute	Description
Table		The data table that you want to transpose.
Columns	Optional	Specifies the column names or number of columns for the transposed table. This parameter must be null, specify the number of columns, specify a list of column names, or specify a table type.

Description

The Table.Transpose function turns columns into rows and rows into columns. It offers an optional argument to provide the column names of the transposed data.

Examples

Let’s clarify how to use the Table.Transpose function with some examples.

Imagine the following dataset with the name Source.

Transposing a table

Using the Table.Transpose function, we can interchange its rows and columns. The resultant table will have new column names like Column1, Column2, Column3, and so on. The following formulas will give the same result:

Table.Transpose( Source )

Table.Transpose( Source, 4 ) // Specifies the number of columns

Table.Transpose operation shifts the table in Power Query

Remember, when you define the number of columns, it must match the number of columns in the original table. If there’s a mismatch, you’ll encounter an error stating “Expression.Error: The count of ‘columns’ doesn’t match that of ‘columnnames'”.

Naming Columns

For a more user-friendly table, it’s necessary to assign appropriate column names. One way to do this is by navigating to Transform, and selecting ‘Use first row as headers‘.

But there’s another method. The second argument of the Table.Transpose function can be utilized to directly generate column names. The simplest way to do this is by referring back to the ‘Name’ column of the previous step. Alternatively, you can manually provide the column names as a list.

Table.Transpose( 
  Source, 
  Source[Name]  // specifies the column names of the transposed table
)

/* ------- You can also manually provide the column names -----------*/

Table.Transpose( 
  Source, 
  {"Age", "Country", "Salary", "Is_Married" }
)

Table.Transpose operation with specified column names in Power Query

After using one of these methods, your table will have the correct column names. If you don’t require the first row, combine the Table.Transpose function with Table.Skip to remove it.

Defining column types

If you need to assign column types to the transposed table, the second argument can be used to specify a table type. For example:

Table.Transpose (
  Source, 
  type table [ 
               Age =        Int64.Type, 
               Country =    text, 
               Salary =     Int64.Type, 
               Is_Married = text 
             ]
)

Table.Transpose operation with specified columns and data types in Power Query M

This will ensure that each column in your transposed table has the appropriate data type. To see this code in action, you can insert the following code in the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxPVdJRMjJVitWJVnLOL80rKaoECvillmSkFuUk5qUUg2WCE3MSwRKmBkAAFvIsjvdNLCrKTE0Bqc9Xio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Value = _t]),
  Transpose         = Table.Transpose ( Source ), 
  NumberedColNames  = Table.Transpose ( Source, 4 ), 
  DynamicColNames   = Table.Transpose ( Source, Source[Name] ), 
  ManualColNames    = Table.Transpose ( Source, { "Age", "Country", "Salary", "Is_Married" } ), 
  TableTypeColNames = 
    Table.Transpose (
      Source, 
      type table [ 
                   Age =        Int64.Type, 
                   Country =    text, 
                   Salary =     Int64.Type, 
                   Is_Married = text 
                 ]
    )
in 
  TableTypeColNames

Other functions related to Table.Transpose are:

Contribute » | Contributors: Rick de Groot, Mahmoud Bani Asadi
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-transpose