Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Introduction to Data Explorer for Excel


By:   |   Last Updated: 2013-05-16   |   Comments (7)   |   Related Tips: More > Microsoft Excel Integration

Problem

I have been hearing about a new tool from Microsoft called Data Explorer. I would like to know what this tool is all about, how to install it, and how to get started with it.

Solution

Data Explorer is an add-in for Microsoft Excel and is used for data access and data discovery by Business Intelligence (BI) Professionals and Information workers, and improves the overall experience of these users/professionals.

Data Explorer allows users to do the following:

  • Identify and extract the data from various different sources like Relational Databases, Files, OData Feeds (Open Data Protocol), Web Pages, Facebook, Windows Azure Marketplace, Hadoop Distributed File System (HDFS), SharePoint List, and Active Directory etc.
  • Combine data from multiple data sources and prepare it for further analysis and reporting.
  • Import the data into Excel and work with it using tools like Excel, PowerPivot, and Power View etc.

In this tip, we will cover the following:

  • Installing the Data Explorer Add-in
  • Different Data Sources Supported by Data Explorer
  • Extracting data from Facebook
  • Applying basic transformations
  • Creating a Simple Report

Installing Data Explorer Add-in

Let's first install the Data Explorer Add-in for Excel 2010. This add-in is currently in Preview, so we will be installing the Preview Version which is available for the general public. Preview is available for Excel 2010 and Excel 2013.

For this demonstration, we will install the Excel 2010 add-in. Follow the below steps to install the Data Explorer Add-in.

  • Go to Download "Data Explorer" and take a look at the pre-requisites.
  • Go to Microsoft "Data Explorer" Preview for Excel and click on the appropriate download links depending upon the version of Office/Excel installed on your computer (32-bit/64-bit) and save the executable file on your computer. Make sure to also take a look at the Release Notes to know more about the features, limitations, known issues, and other details pertaining to this release of Data Explorer.
  • Close all the open instances of Excel on your computer (make sure to save your work before closing).
  • Double-click on the executable (.msi) file, click on Run, and install the add-in.
    • Click Next on the welcome dialog
    • Read through the license agreement and accept it and click Next
    • Select the Installation Directory/Location and click Next
    • Click on Install on the Ready to Install Microsoft "Data Explorer" Preview for Excel dialog
    • Click Finish on the Completing the Installation Wizard dialog
  • Now open Microsoft Excel and notice that there is a new "Data Explorer" tab added to the Excel Ribbon as shown below.


  • Newly added Data Explorer Tab in Excel Ribbon

Different Data Sources Supported by Data Explorer

 Below is a snapshot of various sources supported by Data Explorer.

List of Data Sources Supported by Data Explorer

Extracting Data from Facebook

Let's extract data from Facebook. For this demonstration, let's extract the posts by MSSQLTips on its Facebook Page.

  • Go to MSSQLTips Facebook Page.
  • Copy the serial number at the end, which in this case is "155636491141708". For pages where this has been modified, we need to take the last word like for Datta's Ramblings Facebook Page, we need to take the last word, which is "DattasRamblings".
  • Open Microsoft Excel, go to "Data Explorer" tab, click on "From Other Sources" button, and select "From Facebook".
  • In the Facebook dialog box, enter Serial Number copied previously ("155636491141708") into the "Username or object ID" text box and select "Posts" from the "Connection name" drop down, and click Apply.


  • User Name and Connection for Facebook Data Extract

  • This will open up a "New Query" window with data from Facebook loaded into the window.


  • Data from Facebook Loaded into Data Explorer New Query Window

  • Notice that, due to limitations of the preview version of the Data Explorer, we get a message at the bottom of the "New Query" window, which reads "The data in the preview has been truncated due to size limits. Reducing the number of columns or filtering may load more data." as shown below.


  • Message about Data Size Limitation in Preview Version

Applying Basic Transformations

Next let's apply some basic transformations, similar to Substring in T-SQL or Derived Columns in SSIS. For the sake of this demonstration, let's derive the Post Year and Post Month columns from the Posted Date column ("created_time" column in the extract").

  • At the end of previous section, we can see that, we get the message, description etc. columns as part of the extract. Click on "id", holding the control key, click on "created_time". Right click with this selection and select "Hide Other Columns" from the context menu. This will remove/hide all the other columns except "Id" and the "created_time" from the Data Explorer Query Window. At the end of this step, our "New Query" window should look as shown below.


  • Removing/Hiding the Unwanted Columns

  • After the above step, when we scroll down, we can see that we have more records loaded into the window, which is due to the fact that we have less columns in our query/window as suggested by the message in the previous section.
  • Right click on the "created_time" column, select "Split Column" followed by "By Number of Characters..." from the context menu.
  • In the "Split a column by position" dialog, enter "7" in the "Number of characters" textbox and choose the "Once, as far left as possible" radio button under the "Split" section, and click on Apply.


  • 'Split a column by position' dialog

  • After the previous step, the "created_time" column is split into two columns "created_time.1" and "created_time.2".


  • 'created_time' split into 'created_time.1' and 'created_time.2'

  • Also notice that, each of our actions adds a step in the right side Steps pane as shown below. If we want to revert a particular step, we can use this pane.


  • Steps Pane in Data Explorer

  • Similarly, notice that, as we perform changes to the columns, the formula in the formula bar (Represented by "fx" like in Excel) keeps on getting updated.
  • Now, remove the "created_time.2" column from the query. Right-clicking on "created_time.2" column and select Hide from context menu.
  • Next let's split "created_time.1" column and separate Year and Month parts. Right-click on "created_time.1" column and select "Split Column" followed by "By Delimiter..." from the context menu. In the "Split a column by delimiter" dialog, set the "Select or enter a delimiter" drop-down to "Custom", enter "-" (Hyphen) in the next textbox, and choose "At each occurrence of the delimiter" under Split section.


  • Split a column by delimiter

  • After the previous step, "created_time.1" column is split into "created_time.1.1" and "created_time.1.2" containing Year and Month respectively. Right-click on the columns and rename "id" to "PostId", "created_time.1.1" to "PostYear", and "created_time.1.2" to "PostMonth". At the end of this step, our query window will look as shown below.


  • Final output with Year and Month separated

Creating a Simple Report

In this last section of the tip, let's create a simple chart/report in Excel. In the query window, click on "Done" at the end of last step of previous section, and that will load the data into an Excel Sheet as shown below.

Required data loaded into Excel Sheet

Now go to Insert tab in Excel ribbon and insert a Pivot Chart. After the Pivot Table and Chart are added to Excel, plot Count of PostId against PostYear and PostMonth by dropping the appropriate fields into the appropriate areas. The completed chart looks as shown below.

Final Completed Pivot Table and Chart in Excel

As we can see, the data from social media sites like Facebook can be extracted, transformed, and loaded into Excel for Reporting Purposes. This extraction can be done within a matter minutes with a few simple steps as demonstrated above. From this demonstration, we can clearly see that this tool greatly helps in accessing and preparing the data for further analysis and reporting.

Note: This demonstration is based on the preview version of Data Explorer for Excel. Features in the final release might vary from what has been demonstrated above.

Next Steps


Last Updated: 2013-05-16


next webcast button


next tip button



About the author
MSSQLTips author Dattatrey Sindol Datta has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, December 15, 2016 - 11:58:56 AM - Rayhere! Back To Top

 

 NOTE:  Data Explorer aka Power Query is now known as Get & Transform in Excel 2016 and can be found under the Data tab.


Monday, June 22, 2015 - 9:56:35 AM - Arun Back To Top

How Do I import data from Oracle data base to Excel  by passing three parameters . 1 string parameter others are date parameter. Please do the needful help.


Tuesday, December 30, 2014 - 6:24:47 PM - Houston VanHoy Back To Top

Data Explorer is now called Power Query.  I am surprised that you sent this headline about Data Explorer on today's email.  See article below:

 

http://blogs.msdn.com/b/dataexplorer/archive/2013/07/06/quot-data-explorer-quot-is-now-microsoft-power-query-for-excel.aspx

 

 

 


Friday, July 19, 2013 - 11:00:43 AM - Dattatrey Sindol Back To Top

Hi There,

No. The output results can go to excel worksheet and not to database.

 

Best Regards,

Dattatrey Sindol (Datta) 


Thursday, July 18, 2013 - 8:03:08 PM - 8G Back To Top

Can the output query results go directly to a database (MSSQL for example)?


Friday, May 17, 2013 - 7:50:56 PM - Neeraj Mittal Back To Top

Excellent post on explaining Data explorer use !!!


Thursday, May 16, 2013 - 2:05:30 PM - Srinath Back To Top

Good piece of information !! Thank you for such well written article Datta !!!


Learn more about SQL Server tools