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 🙂

 

 

 

  • Interesting. Gonna try reproduce it in Business Objects 🙂

    PDeRop

  • borja Gonzalez

    I think this is not the real solution. with this trick the result is not dynamic.Instead present the data with that format you can select the report option (repeat all elements) and after that Select OLAP TOOLS -> convert to formulas. Now the result is dynamic and you can load the table in powerpivot (this is the real trick). Everytime the data is refreshed in the source all the data is refreshed in the formulas and powerpivot.
    Works like a charm, besides you can select this data from another powerpivot file to make more complex analysis.

  • Kasper de Jonge

    Thanks @borja Gonzalez , ill try that out !

  • Please see link below for an automated unpivot VBA utility. It can exclude blanks and zeros from the final database and can be used to convert 100s of files during an automated process.

    http://www.spreadsheet1.com/unpivot-data.html

  • Carly Kaufman

    @borja Gonzalez
    Borja, I tried to implement your suggestion, however, OLAP TOOLS is grayed out. I also do not understand what you mean when you say “select the report option”. Please advise.

  • Bob Phillips

    A simpler way to repeat the items is to select the header cell in the pivot, right-click and goto Field Settings>Layout & Print>Repeat item Labels.

    But … it can all be done more easily.

    Select a cell within the table then

    Alt-D>Alt-P>Multiple consolidation ranges>Next>I will create the page fields>

  • Carly Kaufman

    @Bob Phillips
    What version of Excel are you using?

  • Power Query can do this out of the box!

    This article lead me to the solution:

    http://office.microsoft.com/en-ca/excel-help/unpivot-columns-HA104053356.aspx

    I formatted my crosstab as a table. Then on the Power Query ribon selected “From Table – Excel Data”

    Power Query came up with a dialog box where I selected columns 2 onwards, rightclick and select “Unpivot”

    This created an unpivoted table on a new sheet.

    Even had the option to add the resulting table to the data model – exactly what I was looking for!

    • Kasper de Jonge

      Thanks, yes it can do this now. Power Query didn’t exist back then 🙂