Power Query Delivers ETL Features in Excel

The Power Query add-in for Excel 2013 enables expert users to extract, transform, and load data from databases and many other sources for self-service business intelligence (BI), analysis, and other applications. Power Query plays an important role in creating interactive reports for Power BI, Microsoft’s hosted BI service. However, Power Query is designed for individual data transformation in Excel and doesn’t have the advanced features available in server-based tools (such as SQL Server Integration Services) that provide audit controls to protect company data, output to data warehouses for archiving, and scalability to meet high-end performance requirements.

Self-Service Data Transformation Inside Excel

Power Query is a free extract, transform, and load (ETL) add-in for Excel. The goal of Power Query is to enable expert users, called authors, who are very familiar with Excel and the source data to build sophisticated solutions with minimal IT or developer support. With Power Query, authors can build queries that extract data from a variety of data sources, such as Web pages, SQL Server, and OData feeds from applications. Queries can transform the data (for example, by reordering columns, standardizing date column formats, and replacing column headers with user-friendly terms), which are loaded into an Excel worksheet. For example, an author might create a query in Power Query to combine product sales volumes from one system with financial metrics from another system to generate an approved sales performance table that can be used by analysts to create monthly performance charts. (For an example of a Power Query data transformation process, see the illustration “Power Query for Excel“.) The data loaded into a worksheet can be used to create standard Excel charts or interactive reports in the Power View and Power Map add-ins. The add-ins use the Power Query data schema for filtering and drill-down features.