Transpose or Unpivot Entire Datasets with Data Explorer

So it’s been a while since my last post. I’ve been moving my family again. This time to sunny Columbia, South Carolina. Mrs. Pig’s parents (who are getting on in age) are there, and she wanted to be closer to them. I graciously agreed. It’s nothing a good executor of a Will wouldn’t do.

.

During the move, I’ve been working with Data Explorer. I ran across a new feature in the latest update of Data Explorer that should make all Excel Analysts giddy.

.

It’s a new feature called Unpivot. With UnPivot, you can easily transpose a block of column-oriented fields to be row oriented.

There are several ways to do this with traditional Excel, but Data Explorer makes the task fairly straight forward.

.

Let’s say I have a set of data where months are going across the top. In order to more effectively structure the data, I need the month names in a single column next to their respective values.

.

I start by highlighting my entire range of data, then click the From Table command on the Data Explorer tab.

.

Excel will activate this stylish message box telling me that my data source will be converted to a table. I specify whether my table has headers.

.

After clicking OK, I get an initial Data Explorer Query window that shows me my imported table.

.

Now all I have to do is highlight the columns I want to transpose (in this case, my month fields).

Then I right-click and select Unpivot Columns.


.

Data Explorer does its magic and gives me a data set where months are now going down in its own column (Data Explorer gives this new field a default name ‘Attribute’).


.

At this point, I can right-click on the Attribute field and rename the field to Month


.

Once I click Done on the Date Explorer Query window, I get a new tab with my transformed data set.

I can now copy this data as values to another location.


.

Easy; reliable; no coding; no fuzzy work arounds.

Wow – another compelling reason to install the Data Explorer add-in.

I’m sincerely hoping Microsoft eventually makes Data Explorer part of the native Excel functionality as opposed to an Add-in.

12 thoughts on “Transpose or Unpivot Entire Datasets with Data Explorer

  1. John H

    Very nice post — well written and saves me the trouble of writing it up and passing along to co-workers… I’ll just link to here!

  2. Darin

    I selected my columns and right-clicked, but I don’t see that as an option in data explorer. My right- click menu doesn’t quite look like yours either. Was there an update that I didn’t hear about? (Specifically, the icons in front of the menu items look different)

  3. Paul

    Hi Mike,

    Very usefull!
    but Excel 2010 or 2013 is needed

    You say:” There are several ways to do this with traditional Excel” to transpose column-oriented fields to be row oriented . I know only one – using Multiple consolidation ranges from pivot table. Could You describe the others?

  4. Paul

    Mike: this the way I know. You mentioned that there are others ways, that is way I asked.

Leave a Reply

Your email address will not be published. Required fields are marked *