Microsoft “Data Explorer” Preview for Excel
We’d like to let you know that there is an updated build for Data Explorer available. You should see “Auto Update” on your ribbon light up soon. Alternatively, you can go here to download.
In this build, we are introducing a built-in formula for unpivoting columns to rows. Over the past few weeks, many of you have expressed feedback indicating that the ability to unpivot data would be very useful for cleaning up and consuming data for everyday needs. We are happy to provide this as a simple, consistent and intuitive experience.
As an example, consider the following historical population data obtained from the London Datastore.
While this format works well as a report showing population details by year and area, it is not particularly well suited for consumption in analytic/BI scenarios – especially if you were thinking about aggregating or summarizing the data along different dimensions.
Data Explorer now provides the ability to select the columns you would like to unpivot, and accomplish the task in a single click. Here’s the Unpivot menu option you can apply over a selection of columns:
The result of unpivoting yields the following table:
And a few more Data Explorer transformations gives you a nice cleaned up table that can be used for summarizing, or for consumption in PowerPivot or Power View.
This update of Data Explorer also includes a better experience for browsing and using search results. You no longer have to click on the tiny “Use” link in the search results pane – instead you can click on the search result itself. This was a point of confusion for many users, and we hope this improves the general experience.
As always, there are dozens of bug fixes in the update as well.
We hope you enjoy this latest build. Let us know what you think!
Data Explorer Team
Wow...This seems interesting.
I have few excel sheets (with Data Explorer), where i have created many connection to different data sources, and a complete data model with many relationships and few Power View visualizations. When i upgrade, will that entire structure (particularly the connections to the other external sources) remain same?
Absolutely. This Data Explorer update has full backwards compatibility with previous Data Explorer bits since our Preview announcement (February).
If you hit any issues, please let us know and we will be happy to assist you.
Thanks for trying Data Explorer!
I have noticed an oddity with the Data Explorer, if you use the name of the "Table-Query" that it creates inside a formula, like a "Vlookup" for example, when the Query refreshes the formula loses the name and a #REF! error appear. It is a behavior connected with the Beta release and will be solved with the final release ?
Could you send us a frown with more details so we can follow up on the specific issue?
Very cool and welcomed feature! Thanks!
(My apologies if this gets posted twice. The first attempt disappeared in the blink of an eye.)
I've noticed on two computers now that Data Explorer causes "ghost" VBAProjects to be left in the Visual Basic Editor. It's true for addins and workbooks with or without VBA, even for unsaved workbooks. When Data Explorer is checked in the Developer > Com Add-ins dialog, closing a workbook or addin doesn't remove it from the VBE. Once I uncheck Data Explorer all of the VBAProjects for the addins or workbooks I've closed disappear from the VBE.
Both computers are running Windows7 - one 64-bit, one 32 - and Excel 2010 32-bit. This behavior was true both before and after updating to the latest build - 1.2.3263.4.
I haven't fooled around with Data Explorer much, but what I've seen is quite impressive. Thanks!
This UnPivot feature is going to be a game changer for PowerPivot!