Unpivot data using Microsoft Data Explorer

I have been really impressed with Microsoft Data Explorer the minute I had a chance to play with it as I was able to turn formatted Excel reports into legitimate data sources available for analysis. Fairly recently, an update to the tool delivered an uber critical feature that makes a huge impact on the world of self-service BI. I am talking about the Unpivot.

Let’s look at this table for example (shamelessly borrowed from this Wikipedia page)

Here we see historical and predicted population for several regions of the world. This table serves great purpose at conveying the information, however, if I wanted to do any sort of analysis across all of the time periods, I would have a problem doing it as the data is stored across eight columns instead of one, like this

The new Unpivot feature of the Microsoft Data Explorer allows us to do just that, collapse multiple columns of data into one. (Another common example would be a financial statement where you have financial metrics going down in rows and the amounts going across the columns, one number for each month)

Let’s go through a few steps to get a feel for how it works.

First, let’s fire up Data Explorer and click on the   button on the ribbon. That will bring up find page window where we can paste the URL of our table (http://schools-wikipedia.org/wp/w/World_population.htm)

Click OK and the new data explorer query window comes up that looks like this:

Click on the first table on the Navigator pane and you should get something that looks like this

As you can see, we have our regions in rows and our data in a column for each year analyzed.

Now, let’s unpivot this data, in order to do that, highlight all the year columns and right click on a header of any highlighted column and select Unpivot menu option

The result should like this:

Now you can right click the Attribute column and rename it to say Year and you will have yourself a perfectly consumable data set

You can click Done and then add the data to your PowerPivot model or a pivot table. I think that for anybody who gets data as a pivoted report, this feature will save countless hours trying to massage the data into something a little bit more consumable.