How to unpivot using Excel and load it into PowerPivot

I had an interesting question today where someone send me a workbook that contained a flattened table like this:

Now as a result we want to see the top 1 nr of products per country. The problem here is that we cannot just load this data into PowerPivot and start analyzing to get this result. In this case the columns need to become attributes in a dimension. This is a classical ETL operation that can be done by many tools like SQL and SSIS , but not by PowerPivot as a modeling tool.

So how do you fix this problem ? At first I used a Macro that we found online to unpivot the data, that did get me the result I wanted. But some time later I realized that there is a trick in Excel that I can use that I have blogged about before here, by combining this trick and another it will be pretty easy to unpivot.

In this blog post I will show you how to Unpivot a flattened table using PowePivot and Excel (Actually you would not even need PowerPivot if you don’t want to).

The first thing we do is create a regular excel pivottable out of the table I showed in the first image

Next we change “Count of Product x” to “Sum of Product  x” and change the pivottable into a classic pivottable using Pivottable options:

This will turn it into a column per level:

Next we move the values from Colum to Rows, this results in an unpivoted result.

Next we need to turn the measure “Sum of Product X” into a column. Copy the entire pivottable and past it in a new sheet keeping only the values

This will result in a flat table inside Excel. Now I select the “Sum of Product X” table and use search and replace to replace “Sum of ” with a empty space. I also renamed the product column header:

Now the last thing that I need to do is get rid of the blanks and replace them with the same values as the previous row. Here I can use another Excel trick:

Start by selecting all the columns we want to fill the blanks of, go to the home tab and select “find and select”, and click “Go To special”. Here we can select
blanks and click ok. This will result in all the blanks being selected.  Type =, press the up arrow and press Ctrl+Enter (that’s hold down Ctrl and press the Enter key). All the
empty cells will be filled with the value of one row above. Exactly what we  wanted.

Now we can choose two options, load the table into PowerPivot or just use the regular Excel pivottable. I prefer getting my data into PowerPivot so let’s do that. Select a cell inside the table and go to the PowerPivot ribbon and click Create linked table. This will automaticly create a Excel table (make sure to select my table has headers) and load the data into PowerPivot:

Now we can create a new pivottable based on this table:

As final step we need to determine the Top 1 product per country, again we are going to use an Excel function. Select a product in the pivottable and click on the filter button on row labels. Now we select value filters, click Top 10 and change the number of items from 10 to 1 and press Ok.

This will give us the result we want:

As you can a lot can be done with Excel, you just have to know your way around. While this might not be a surprise for most of the Excel pro’s out there, we BI Pro’s are eager to run things through SQL or SSIS but it can just as easily be done by Excel. Maybe now I will be Excel gansta enough to join Rob Collie’s elite Excel group 🙂