This page includes a video that walks you through the Power Query Unpivoting utility that allows you to take a table, in this example, 195 rows deep and 14 columns wide and convert it to a table 1521 rows deep and 6 columns wide. You will see what that means in the video but here are two screen shots to help you:





Why Unpivot?

Just take a look at the first screenshot above: there is nothing wrong with it as it contains genuine financial statement data and ratio analysis data. However, if you want to analyse the data by pivot table, for example, you would have a lot to do.

By unpivoting I hope you can see that those columns of numbers are transposed into rows. So that all of the data are now in columnar form and really easy to analyse with a pivot table. You can now combine all of the financial statement data and the ratio data in one column and analyse them. You could show, for example, net income values and the net income margins in one pivot table quite easily.

If you use slicers too, things become much more direct and easy! A very rough first draft looks like the following screenshot:



Reverse Pivot Magic was easy to do but Power Query is even easier! I should say that you might, of course, have to organise your original file a little. Firstly, you must convert it to an excel table, making sure you have deleted empty rows and columns and deleting any redundancies such as repeated header rows if there are any.

After that, Unpivoting could become your daily tool!

I am sorry but I cannot let you have this file as the data in it are not mine to share but I am sure you have similar data of your own.

Here is the video: 

Duncan Williamson

%d bloggers like this: