We have started to use the new Microsoft Power Query add-in for Excel that enhances self-service business intelligence for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more.

Microsoft Power Query for Excel is available in two editions:

1. STANDALONE EDITION FOR OFFICE 2010 / 2013

  • Easily discover, combine, and refine data for better analysis in Excel.
  • Import data from external data sources
  • Shape data from multiple data sources
  • Filter, sort, and group data

2. POWER BI PREVIEW EDITION FOR OFFICE 365

In addition to the features in the standalone edition

  • Securely share your data queries within the enterprise
  • Manage shared queries
  • Find and use a shared query
  • View Usage Analytics for Your Shared Queries

This powerful tool can be very quickly combined with simple Pivot Tables or Pivot Charts to produce rapid results.  It can also be used with PowerPivot, Power View and Power Map to perform more complex data integration and impressive visualisations.

Power Query is a part of the Microsoft Power BI for Office 365 offering, more info can be found here.