The Transpose Function

Private: PowerQuery for Finance Teams The Transpose Function

The Transpose Function

Previous Session Summary

In the last lesson we looked at how to use the fill function to fill in ‘null’ rows with the directly previous or directly following cell value as well having a look at how to append reports together that have different column names.

Lesson Synopsis

In this lesson we’re having a short break from the logistics file we’ve been working with, to have a look at a new short dataset where we can use the Transpose function. The Transpose function is very straightforward as it allows us to switch over the X axis and Y axis of a table of data.

Resource Listing

The Video

Play Video

Lesson Notes

In this lesson, we’re going to move away from the container arrival data and invoice data that we’ve been working on in the last few lessons. In a different set of data we’re going to look at the useful transpose function. This function is very similar to the TRANSPOSE function that we have in standard Excel which will move data from a column format into a row format and vice versa.

Let us imagine that we need to change some data so that the headers and first column have swapped place…something like the below:

In the above example, we want to swap round the department names and month names so that the list of departments is on the top of the table, while the month names are down the side of the table.

To do this we can use the Transpose function.

How to use the Transpose function

To do a simple transposition, the first thing to do is get rid of any rows above, or columns before, the data we wish to transpose. In our example above, this would be the row with the value ‘Headcount’ and the first column with the word ‘Department’ as those will make a mess of our transposition. It is possible to include them in a transposition, but that’s a little more advanced, so we’re going to leave it for the moment.

To remove the top row:

a) select Remove Rows
b) then remove top rows and put in 1 in the quantity box.

To remove the first column:

a) highlighting the column,
b) right click the header and select Remove Column.

Right, now we can move on to transposition.

So making sure that there isn’t any of our data in the headers (so we want the headers to just be called Column1, Column2 and so on), go to the Transform tab and select Transpose.

And that’s it! We’ve now got the axes moved around.

.... and a little more!

Nothing else to add here – next lesson please!

Previous Lesson

Back to Course

Next Lesson