March 1, 2013

Power Query for Excel: The Next Piece Of The Self-Service Puzzle

Microsoft released the preview of Power Query Data Explorer for Excel, a new add-in for Excel 2010 and 2013 that promises to bring powerful capabilities for searching, transforming, shaping and merging data to excel users. A clear sign that Microsoft’ strategy to democratize BI is still in full swing and perhaps aligned with industry demands for Business Intelligence self-service platforms.

From Data Explorer preview download page:
Microsoft “Data Explorer” Preview for Excel is a new add-in that provides a seamless experience for data discovery,
data transformation and enrichment for Information Workers, BI professionals and other users.

Do you mean  Self –Service ETL?

Maybe, but in any case Data Explorer sounds a very exciting proposal given that data integration is the biggest bottle neck most BI initiatives experience, and where more often users must rely on technologies and skillset accessible only via IT departments.

Data Explorer First Glance

Once it is installed, Data Explorer manifest as new tab in the Excel ribbon.

From there you can do:

Online Search

Search for data sources on the web – at the moment all results seemed constraint to Wikipedia pages


Get External Data

These options provides the ability to connect and import data from a number of sources. A query is the unit of work when getting external data – a query contains 1 or more steps
    • From Web: Once you provide a URL, it allows you to import data from a webpage in tabular format. I was able to easily obtain data from quite a few pages (e.g Zip Codes with the Highest Median Household Income in the United States), although I imagine there are constraints as how the data is layout in the page for data explorer to work.
    • From File: quite a few options to load data from files in different formats, including XML and Excel.
    • From Database: a predefined list of major relational database types. I found some major players missing from the list such as Sybase and Netezza – I would imagine support for additional providers will be added later on.
    • From Other Sources: several interesting options here such as SharePoint lists, Odata and HDFS

Notice that each time you use these options a new query is created and then you are given the opportunity to transform, manipulate and shape the data. What is even more powerful is that each action is recorded in the form of a ‘step’ that get replayed each time the query is refresh. Query steps are expressed via formulas that are based new set of functions. See “Data Explorer” formula reference for more details.

Combine Data

Once data has been imported you can combine it.
    • Merge: Allows you to bring columns from 2 different queries together – similar to a  JOIN in the SQL relational world.
    • Append: Creates a new query with all records of a first query followed by the rows of a second one – similar to what a UNION ALL does in the SQL relational world does.

I created a quick sample workbook that gets data from Northwind Odata feed and another external web page (top Zip Codes with the Highest Median Household Income in the United States). It is not the most meaningful scenario but will let you get the general idea of how this stuff works. You can see a copy this sample here:

What’s next?

You can download Data Explorer and start learning about it:
Microsoft "Data Explorer" Preview for Excel
Data Explorer Help
Microsoft "Data Explorer" Preview for Excel blog


Data Explorer could play a key role in Microsoft self-service BI platform  as it reduces the friction typical BI users experience when searching, collecting and connecting source data. However, as a component of a major system it will be interesting to see what steps Microsoft will take. Few things come to mind
  • What would the “sharing” story be?
  • What would the progression/graduation from self-service to  IT-managed solution looks like? Perhaps a Data Explorer to SSIS path – I am sure many of us will like that
  • Would we get a tighter integration with the other Excel “Powers” (PowerPivot & PowerView)
  • Will it integrate with Data Quality and Master data Services?
  • Will IT be given tools that allows it to have the some sort of oversight ?

No comments:

Post a Comment

I will love to hear your feedback and comments. Thanks.