5 Cool Things You Can Do With Data Explorer

March 22nd, 2013 by datapig Leave a reply »

It's time to learn something new. Microsoft recently released a new Add-in called Data Explorer.

This is a free Add-in for both Excel 2010 and Excel 2013 that can be downloaded here.

Data Explorer gives us a new Ribbon tab where we can import, transform and merge data from a wide variety of sources. In addition to standard data sources like Microsoft Access, SQL server and Text files, Data Explorer enables the importing of sources such as Active Directory, Azure, OData and Hadoop.

.

In this post, I'll give you a few examples of some of the cool things you can do with Data Explorer today.

.

Example #1: The Basics

After installing the Add-in, you'll see a new Data Explorer tab.

In the Get External Data group on the left, you see lots of choices to choose from . In this first basic example, I'll choose From Web and enter the site I'm interested in.

.

After a bit of gyrating, a query window activates, showing you the available tables found on the site.

You can click one to see what the data looks like.

.

You can clean up the data table right inside the query window.

Here, I'm right-clicking a column and electing to use the first row as the column header.

.

Next I'll right-click on the Trend column and elect to hide it.

.

Note the formula bar above the results. This formula bar contains the syntax needed to run the steps you select. The 'Steps' pull-out allows you to review or remove each action you've applied to the query. You can also update the formulas by hand by using that nifty paper icon (more on that later).

.

Once you've confirmed your changes, Excel will bring your data to the spreadsheet. Here, I've applied some Conditional formatting to make it look nice. As you might guess, you can right-click on this table and refresh the data anytime you'd like.

.

.

Example # 2: Manipulating Data Feeds

Data Explorer has a mechanism to allow you to search for random web data that may be out there.

For example, suppose I wanted to know how many radio stations in Maryland are sports stations? I can find that information using Data Explorer.

.

I click on the Online Search command and put in my search term "radio stations maryland".

I immediately see a list of approximate matches. Clicking on one lets me see the data on my spreadsheet.


.

I like what I see so I call up the query window.

Here, I can choose to hide all but the Format column.

.

Next, I can Remove Duplicates.

.

Finally, I'll add a "Count of Rows" column to the table by using the Group By feature.

.

After confirming my changes, I get a data table that answers my question. There are 9 radio stations in Maryland that are sports stations.

This is unbelievably good stuff. With a few clicks, I searched the internet, found some base data, and manipulated it to suite my needs.

.

.

Example #3: Get a list of Files in a folder

Ever needed to get a list of files in a folder? I have. I've actually created VBA code to do this. Well, Data Explorer lets me create a dynamic list of files with just a few clicks.

I select the From Folder option and enter my target folder's path.

.

This immediately activates the query window with a view into all the files in my selected folder.

.

I can see a drill icon that lets me expand the Attributes column to show additional information. In this case, I want to include the Kind and Size fields.

.

After confirming my changes, I get a refreshable table which lists the files I have in my folder.

I'm not sure this is exactly what the 'From Folder' option in Data Explorer was intended to do, but I find this feature extremely handy.

.

.

Example #4: Get a List of People and Emails in your Company

Ever wish you could take the company Address List in Outlook and put it into Excel?

Well, Data Explorer gives you a way to do just that with the From Active Directory option.

.

I can't show you the query window for this option (due to confidentiality reasons), but it relatively similar to the previous examples.

In any case, Data Explorer lets me reach into Active Directory and extract data like email address, employee, department, and telephone number.

The end result is a refreshable alpha roster of employees and contact info.

Again, I would normally need code to pull this off. Amazingly, Data Explorer does it for me with a few clicks.

.

.

Example #5: Creating one Table from Multiple Excel Files

As I mentioned earlier, each action you take in Data Explorer creates some formula syntax that represents a step in the data retrieval process.

You can click on the paper icon in the query window to open a dialog box that shows you the full syntax of your Data Explorer query.

.

Initially, this syntax looks hopelessly impenetrable. But after playing with Data Explorer a bit, you can get a sense of what the syntax is doing. The key is to do some action in your query, then look at the syntax. Do another action in your query, then look at the syntax again. In no time at all, you'll be able to write your own Data Explorer query to perform some complex actions.
(You can also check out the Data Explorer Formulas Page for detailed specs)

.

For example, let's say I want to take the data in two Excel files and bring them into one table. The Excel files are similar in structure (same columns in each).

I don't want to copy and paste the data because I will need to do this action every week. I need something more automated. I decide to use Data Explorer to write a query that pulls the data from both files.

..

First, I need to go the Settings command in the Data Explorer Ribbon and enable Advanced Query Editing.

.

Since my target files are in a network folder, I'll also want to click the Fast Combine command and enable 'Fast Combine' so I can dynamically combine workbooks.  (I wouldn't need to do this if my files were local)

to

.

Now I'm ready to write my query. I select the Write Query option.

.

This option allows me to directly enter the syntax I need.

Here, I'm combining two Excel files into one table, using the first row as the header row.

.

After confirming my changes, I get a table with the combined data from both Excel files.

And the beautiful thing is that I can simply refresh the table anytime I need to.

.

One last thing. If you send a workbook with Data Explorer queries in it to someone who does not have Data Explorer installed, they will be able to see the returned query results, but will not be able to refresh the results.

.

.

After playing with Data Explorer for a few days, I've got to say that this definitely an exciting development in the Excel BI story.

Data Explorer will allow Excel analysts to easily integrate data that, in the past, would require personnel and skillsets found only in the IT department.

.

The 5 examples I've listed here only scratch the surface of all the cool things you can do with Data Explorer.

Here are some links to other blogs demonstrating Data Explorer awesomeness.

Chris Web Finds Shakespeare's favorite Words

Dan English Pulls IMDB info with Data Explorer

Jason Maps UFO Sightings

NOTE:  As of July 2013, Microsoft has changed the name of Data Explorer to Power Query.

Advertisement

39 comments

  1. Jeff Weir says:

    After a bit of gyrating, a query window activates

    Spooky. How fast were you going? Did you try gyrating faster, to see if the query window came faster?

    Seriously great post, Mike.

  2. Interesting. I hadn’t found that one. I’ll give it a try tomorrow.

    Your wrote:

    After a bit of gyrating, a query window activates, showing you the available tables found on the site. You can click one to see what the data looks like.

    Does that mean it only recognizes data within a “left angle bracket” TABLE “right angle bracket” tag?That’s the big problem with web queries. Most sites make their tables with CSS. Or does Data Explorer overcome that limitation?

  3. To answer my own question… Data Explorer CAN get the table from this site:

    http://investing.money.msn.com/investments/exchange-rates?

    And the plain old “Data – From Web” command can’t do it.

  4. datapig says:

    Jeff: Thanks for the laugh.

    John: Great point. Data Explorer seems to find tables that the old Web Query feature can’t.

  5. Excellent post!

    Just a note that the Data Explorer Formula Language is actually documented. The language spec, which is also linked from the in-product help (though two levels nested), can be found here:

    Data Explorer Formula Language Specification

    There is also a companion document for the function library available to our language:

    Data Explorer Formula Library Specification

    Clemens (working on Data Explorer)

  6. datapig says:

    Thanks Clemens

  7. sergey says:

    once the query is ready can you send the workbook to somebody that doesnt have data explorer installed?

  8. ross says:

    Great post Mike, looks like a really useful tool.
    thanks
    Ross

  9. datapig says:

    sergey: If you send a workbook with Data Explorer queries in it to someone who does not have Data Explorer installed, they will be able to see the returned query results, but will not be able to refresh the results.

  10. Will says:

    First of all thanks for the great post Mike, this Add-in looks awesome and I can’t wait to play with it. Second, I’m glad Microsoft didn’t confine this to just Pro Plus, like certain other features.

  11. Neil says:

    Looks really useful.

    I’ve just installed it, and I’m starting to experiment. I can’t find an obvious method of manipulating the ‘From Web’ query with VBA, like you could with webqueries. Activating the macro recorder and then pulling data from the web with Data Explorer doesn’t generate any useful code for me to tweak.

    I often use VBA to build the URL based on various parameters, which I then retrieve using .

  12. Neil says:

    Doh, last line of my post got cut off. Should have read…

    I often use VBA to build the URL based on various parameters, which I then retrieve using:
    Range(“A1″).QueryTable.Connection = “URL;” & strURL

  13. Chris Webb says:

    Neil, I showed how to read data from your Excel worksheet and combine it with other Data Explorer queries here:
    http://cwebbbi.wordpress.com/2013/03/04/calling-a-web-service-from-data-explorer-part-2/

    HTH,

    Chris

  14. Doug Glancy says:

    This is very fairly amazing.I just downloaded Data Explorer, so your post was timely and helpful. Thanks. I’ve used VBA in the past to do #s 3 to 5, but maybe won’t be in the future!

    I see that the resulting tables act like other Excel 2010 Tables/Listobjects. For example, I can add calculated columns, the table keeps it’s sorted order when new files are added (#3), etc.

    One thing I noticed is that I’m not able to change the table’s name using the Table Tools > Design > Table Name box. But I can change it in VBA with Activecell.ListObject.Name = “tblTemp2″. Once I do that, then I can change its in Excel.

  15. Doug Glancy says:

    I notice now that changing the table name kills the ability to refresh the table or edit the query.

  16. datapig says:

    Doug: As far as I can tell, the best way to change the name of a Data Explorer query is to use the Name Manager.

    Formulas->Name Manager->Edit.

    When I change the Table name this way, I can still refresh my query. However, I can no longer edit the query.

  17. AlexJ says:

    On table renaming, there were a couple of items in the release documents indicating that renaming tables would prevent refreshing using Data Explorer.

    Is there any more “how to” doc out there? I was playing with both the Active Directory query and the Table function, but I’m a little confused on the right approach for each.

  18. Sean C says:

    Awesome post Mike! If you’d like to try the tool out with an extended selection of datasets, I’d recommend checking out our site: http://www.quandl.com

    We have over 4 million freely accessible datasets that could be pretty fun to pull into Data Explorer.

    Feel free to give me a shout if you have any questions.

  19. Neil says:

    Chris – thanks, very handy!

  20. Ted says:

    Using plain old get data from web on site (http://investing.money.msn.com/investments/exchange-rates?) can get the data.

    I think you need to try harder.

  21. Paras Doshi says:

    Great tips! Having used the Data Explorer when it was hosted via SQL Azure LABS, I was searching for a way to “transform” data – and after reading this, I now know how to do it in excel add-in too. Thanks.

  22. Doug Glancy says:

    I installed the Data Explorer addin shortly after reading this. Yesterday I noticed every VBA Project – be it workbook or addin – was leaving a “ghost” in the VBE after I closed it. Turning off Data Explorer in Developer>Com Add-Ins solves the problem. Updating to the most recent build, 1.2.3263.4, didn’t solve the issue. Windows 7 32-bit, Excel 2010.

    I’m also seeing some interesting error messages when I start Excel, that I think are also from Data Explorer. I can’t remember what they are, but they have that Excel 2013 look and sound “databasey.”

  23. Howdy! I understand this is sort of off-topic but I needed to ask.
    Does managing a well-established blog such as yours take a
    massive amount work? I’m completely new to writing a blog but I do write in my diary everyday. I’d like to start a blog so I can
    easily share my own experience and thoughts online. Please let
    me know if you have any recommendations or tips for
    new aspiring bloggers. Thankyou!

  24. Alan says:

    Great post. Data Explorer is very impressive. Self Service ETL.

  25. Neil says:

    I’m still struggling to automate this with VBA.
    I tried using the macro recorder but the code that was generated didn’t include the URL or any of the ‘steps’.

    Is there anyway via VBA to use Data Explorer to open a URL that is generated via cell references or VBA variables?

  26. Dave says:

    Hi Mike, I’ve been looking for a way for Excel Web Query to download an attached file from a web page, not a table or data embedded in the html. Either using VBA or not but so far I have not been successful.
    Do you think Data Explorer might be more able to do this?
    As an example we have quite a few internal web pages and applications (databases I guess) at our company and you can do manual searching to create data extracts and these can then be downloaded directly into excel by clicking on an option. What I would like to do is a get a web query or VBA script to automatically choose these downloads – have you come across this use before for Data Explorer or Web Query? Many thanks for the great Blogs by the way. Dave

  27. John C says:

    So, I tried to acquire this nifty add-in – clicking on numerous links to the Microsoft site.. seems they no longer have this add-in, nor can Bing Search find any useful link – Sorry Page is Unavailable ..

    Can someone give me link that actually works. THANKS

  28. datapig says:

    John: Here it is
    http://www.microsoft.com/en-us/download/details.aspx?id=39379

    I’ve updated the link above as well.

  29. Kevin says:

    The link for the down load goes to Microsoft Power Query for Excel. Is this correct?

  30. Kevin says:

    This only works on “•Office 2010 Professional Plus (with Software Assurance for Office Professional Plus through Volume Licensing)”.

    So you really need 2013 unless you are in a corporate environment.

  31. David P. Evers says:

    Also, only works on Windows Vista or 7, not XP–the corporate workhorse.

  32. Hiren says:

    What about Office 365 ?

  33. Wow that was odd. I just wrote an really long comment but after
    I clicked submit my comment didn’t appear. Grrrr…
    well I’m not writing all that over again. Regardless, just wanted to say excellent blog!

Leave a Reply

Powered by sweet Captcha