Wednesday, October 1, 2014

Power Query

Pulling data from sources outside of Excel is a fact-of-life for many Excel users.  In the past, Microsoft Query was the Go-To add-in for importing data of this sort into Excel, but it had several limitations.  It is therefore exciting to see that Microsoft is offering a New (Free!) add-in called Power Query!

Microsoft Power Query for Excel provides an intuitive user experience for uncovering, combining, and refining data across a wide variety of outside sources. The list of possible sources is shown below, and even includes FaceBook and Wikipedia:
  • XML file
  • Text file
  • Web page
  • Facebook
  • Wikipedia
  • Sybase Database
  • Teradata Database
  • SharePoint List
  • OData feed
  • CSV file
  • SQL database
  • Microsoft Exchange
  • SQL Server database
  • Azure SQL Database
  • Access database
  • Oracle database
  • IBM DB2 database
With Power Query, you can create, share, and manage queries from search data available inside and outside your organization. Users of this powerful tool can find and use shared queries to mine the underlying data in the queries for their data analysis and reporting.

Using Power Query you can:
  • Zero in on the specific data you care about from your sources
  • Further discover relevant data using the search capabilities within Excel
  • Combine data from multiple, dissimilar data sources and prepare it for further analysis with tools in Excel and Power Pivot
  • Share the queries that you created with others within your organization
If you are a Power User and your job involves analysis of information stored in other sources and formats, you will likely want to explore Power Query.  It is, of course, all about Power (and did I mention it is Free?!?)

No comments: