M Query Guide

Transformations

M Transposing Tables

Transposing Tables

M transposing tables uses Table.Transpose for row-column swap.

Introduction to Table Transpose in M

Transposing a table in M is a common operation used in data transformation processes. It involves swapping the rows and columns of a table, effectively rotating it. This operation is particularly useful in scenarios where you need to pivot data to make further analysis easier.

In M, the language used in Power Query, you can achieve this transformation using the Table.Transpose function.

Understanding Table.Transpose Function

The Table.Transpose function is designed to take an input table and output a new table with rows and columns exchanged. This can be very useful when you need to prepare data for analysis or visualization.

Here's the basic syntax for Table.Transpose:

Simple Transpose Example

Let's look at a simple example of transposing a table. Suppose you have the following table:

  • Original Table
ProductPriceStock
Apples1.20100
Bananas0.80150

By applying Table.Transpose, the resulting table will have rows and columns swapped.

Result of Transposing

After transposing, the table will look as follows:

  • Transposed Table
Column1Column2
ProductApples
Price1.20
Stock100
ProductBananas
Price0.80
Stock150

Notice how the rows have become columns and vice versa.

Use Cases for Transposing Tables

Transposing tables can be particularly useful in the following scenarios:

  • Data Preparation: When preparing data for input into other systems or formats that require a different structure.
  • Pivoting Data: To convert long data to wide format or vice versa for analysis.
  • Report Generation: When the presentation of data requires a different orientation.

Conclusion

Transposing tables in M using the Table.Transpose function is a powerful tool in data manipulation. Whether you are preparing data for analysis or adjusting it for presentation, understanding how to transpose tables efficiently can significantly enhance your data transformation process.

Transformations

Next
Combining Tables