Transpose Data in Excel Using Excel Power Query

Transpose Data excel function is used to change the orientation of the data in excel. By changing the orientation, it means to convert the vertical data (i.e. data from top to down) into the horizontal form (i.e. from left to right) or vice versa (horizontal to vertical). Additionally, this feature is also useful for changing the orientation of an entire excel table, i.e. convert the column headers of an excel table into row headers and the row headers into column headers. In this tutorial, we would learn how to use the excel power query feature to transpose an excel table.

Readers also view Transpose Function in Excel blog to learn 2 advanced ways of transposing data in excel.

Sample Excel Table to Transpose Data

The below image shows the number of units sold by a company during a week in four regions.

Sample Data to Transpose Table Power Query

Let us learn how to transpose this table using the Power Query Editor window.

2 Step Procedure to Transpose Data Using Power Query

The Excel Power Query feature is an amazing tool provided by Microsoft in newer versions of Excel. Are you using older Excel versions? Then check for the compatibility here.

For me, the Power Quer feature is like a lifesaver tool provided by Excel. The basic purpose of this feature is to import and get data from excel tables, ranges, and outside excel sources like Text files, JSON, PDFs, etc. into an Excel workbook. Also, you can transform the imported data as per your wish and need.

Transposing of excel table is one of the other transformations that the power query feature can do for you.

It involves two steps:

  1. Importing the excel table into the power query editor window
  2. Perform Transpose activity on this imported table

1# Importing Excel Table into Power Query Editor Window

In order to import an excel table/range into the power query editor window, it is important to understand that the power query editor window does not accept any normal dataset ranges. To use the power query editor window for range, the dataset needs to be in a proper excel understandable table format.

To convert a normal dataset into a proper excel format, select the table range, and follow this path – ‘Insert’ > ‘Tables’ group > ‘Table’ option (as shown below).

Create Table in Excel Navigation

More details on Excel Table Format can be found in this link.

Now, once a proper excel table is ready, we can easily import this excel table into the power query editor window.

Click anywhere on the excel table that you want to import to the power query editor window and then go to Data > Get Data > From Other Sources > From Table/Range.

Get Data From Table Range Navigation

The excel table (to be transposed) is now in the Power Query Editor window, as shown in the image below.

Excel Table in Power Query Editor Window
How to Transpose Data in Excel Power Query

2# Transforming Table in Power Query Editor Window

The transformation of imported data is very easy when it comes to using Power Query data transformation options.

The Transpose option is available under the ‘Transform’ tab > ‘Table’ group.

Transpose Feature in Power Query Editor

If you click this icon, the power query editor window would instantly move the row headers as columns.

But did you notice that the ‘Transpose’ option did not move the column headers (North, East, West, South) as the row headers? Column headers are missing while transposing the table in Power Query.

Using Transpose Option on Table Power Query

The reason for this weird excel behavior is that the transpose function does not apply on any header row.

Follow the below steps to fix this issue.

  • Under the ‘Applied Steps’ section of the Power Query Editor window, click on the ‘Delete’ icon just beside the step ‘Transposed Table’. This would delete the recently applied step and bring back the table in the power query to original.
Delete Transformation Step Power Query
  • Now, make and convert the header row as a normal data row. To do so, go to ‘Transform’ > ‘Use First Row as Headers’ > ‘Use Headers as First Row’.
Use Header as First Row Option Power Query
  • This would instantly make the header row (North, East, West, South) the first data row of the table. See the image below:
Use Header as First Row Result
  • Now, again try to use the ‘Transpose’ option.
Transpose Feature in Power Query Editor
  • Consequently, you would notice that both the column headers and row headers are now correctly transposed.
Final Power Query Transpose Result
  • Again convert the first row as the header row in the power query editor window. To do so, use the option ‘Use First Row as Headers’ (Transform Tab).
Use First Row As Headers in Power Query Excel
Final Result of Transpose in Power Query Excel

Finally, let us now quickly load this new transposed table back to the excel worksheet. Simply, go to the ‘Home’ tab and choose the option ‘Close & Load’.

Power Query Editor - Close & Load Icon

This brings us to the end of this tutorial on ‘How to use Transpose Feature in Power Query in Excel’ 🙂

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.