With the official review release of the Excel plugin Data Explorer this week, I thought I would give it a little go to see what kind of thing you can do.
Microsoft Data Explorer is an Excel plugin that allows end users do their own self service data collection and data transformations. You can download it from here: http://www.microsoft.com/en-us/download/details.aspx?id=36803 you’ll need Excel 2010 or Excel 2013 though.
Saturday night in the Smillie household is film night, and with the recent Oscars in mind I thought it might be kinda cool to watch an Oscar winner. But which one?
I’m fond of IMDB and I like to watch as many of the top 250 ranked films as possible. (If you don’t know about IMDB’s top 250 then check it out here: http://www.imdb.com/chart/top , seriously, check it out)
So, what better way to play with Data Explorer than to combine the past Oscar Best Film (from here: http://en.wikipedia.org/wiki/Academy_Award) winners with the IMDB Top 250 to see which film me and Mrs Smillie should watch this Saturday.
So, here is how I did it.
First of all, I hooked up Data Explorer to the award winners by adding in the URL above as a Web data source. This presented this screen, where I removed the fields I didn’t want anymore by hiding them (ones in green are the ones I want to keep)
This pulled in the Oscar Best Picture data into my Excel table. Pretty painless and very simple.
Now then, lets repeat this for the IMDB Top 250 (here I did the exact same thing, but just used the different URL). This gave me the IMDB Top 250 films list, which looks likethis.
Now to merge them.
Lets click the ‘Merge’ button. This opens up a screen where I can select my primary data source, what I want to merge with, and what columns (as per below).
So I choose, ‘Best Picture Winner’ from my Oscars data source and I choose ‘Title’ from my IMDB data source.
Click apply and watch it merge…
Oops, no matches. Ah, of course, the IMDB Title has the year on the end. Lets see if Data Explorer can clean that up.
Yup, it sure can. I just edit my IMDB data source (by clicking on my IMDB Table and hitting ‘Filter & Shape’ button) and split the Title on a fixed length from the right.
Then retry the merge
So now the merge works and I am left with 35 Oscar winners that appear in the IMDB top 250 and their IMDB rank. Pretty cool huh. I guess I’ll be watching Godfather this weekend!
I appreciate all this could have been achieved by Copy and Paste, VLOOKUPs, formula,… but the beauty of doing this in Data Explorer is that if the sources change e.g. new Oscar winners or movement in the top 250 films, all I have to do is click ‘Refresh’.
All in all, I am pretty impressed with Data Explorer. It definitely a tool I’m going to use more of.
P.S. Of course I have seen the Godfather already!