Power Query for Microsoft Excel

Recently, my trial of Office 2013 professional plus expired before I was able to install the new excel add in that microsoft released, Data Explorer. Proving how awesome twitter is, I posted my disappointment when I received the following tweet back:

image

!? Data Explorer is available for Office 2010! As soon as I saw this tweet my fingers flew to the keyboard and downloaded data explorer for my excel 2010 that was running SP1. With that, I finally got to take a look at Data Explorer and start playing around with it. For anyone that’s not familiar, Data Explorer is an add-in for Excel that is currently in a preview state, but in my opinion is really going to revolutionize the way that reporting is done in excel. DE (Data Explorer) is designed to hit large datasets in non-traditional sources that you can use to further analyze your business.

Once you download and install Data Explorer, you get a new tab in the ribbon called “Data Explorer”, which is where you interact with the add-in and design your queries. The ribbon looks like:

 

image 

The really nice thing about data explorer is how easy it is to set up. Connecting it to a web site is as easy as knowing what the URL is. For example, I’m a big fan of the EPL, and love watching Manchester United games. The flow and quick pace of soccer keeps my short attention span better than any sport I’ve seen (except maybe hockey). I wanted to import the current EPL table, if you click on the “From Web” section in the ribbon, you will get a screen that looks like the below:

 

image

 

Simply input the web address for the item you want to import, and Excel detects the table relationship on the site and will pull back your dataset. The following screen after you click Apply looks like:

image

You can preview the data that data explorer will import by clicking on the Table on the left hand side. If you connect to a source with more than 1 table, Data explorer will pull back a list of several tables that you can preview and choose which one you want to see. Once you click “Done”, data explorer imports your data into a table that you can then use to analyze your data.

 

Easy and awesome!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s