As part of the Microsoft Power BI suite, Power Query–an add-in for Excel 2010 and Excel 2013–expands Excel’s features with extended data analytics capabilities and the ability to extract and manipulate data from a variety of sources. These additions supercharge spreadsheets and worksheets, coupling standard Excel operations and features like calculations, formulas, graphing tools, and pivot tables with advanced functionality, such as data model creation, using the Excel Power Pivot 2013 Data Model. The add-on is crucial for anyone wishing to stretch the limits of what Excel can do beyond worksheets, desktop databases, or even RDBMs by drawing from all types of new data sources–allowing for unprecedented data analysis and business intelligence.
For the uninitiated, Power Query is a component of Microsoft Power BI, featuring a query language for accessing and filtering various types of data sources. Custom views can be created over this data, and data sources can be merged and shaped to match one’s data analysis requirements. The Power Query add-in can be downloaded for free from the Microsoft site.
Microsoft recently announced three substantial updates to Power Query, as featured in this video:
The following are highlights of these recent updates.
Performance Improvements
– Microsoft has significantly improved the performance of loading queries. According to its benchmarks, queries that before took 10 minutes to execute can now be accomplished in 3-4 minutes with this update.
– Previously, connecting to Excel workbooks from Power Query produced significant latency. With the new update, latency is improved–resulting in decreased preview loading times and an overall more responsive Query Editor experience.
New Dynamics CRM Online Connector
– Previous versions of Power Query connected to Dynamics CRM Online–Microsoft’s SaaS CRM offering–via the OData feed connector. This proved to be somewhat counter-intuitive and hard to discover for many users. Improvements to Power Query’s support of Dynamics CRM Online include a new Dynamics CRM Online connector that simplifies connecting to the CRM.
New Transformations Available
– The Query Editor has undergone a few improvements to make transformations easier. Prior to the latest update, the creation of custom formulas was required to perform certain data transformations such as age and subtract operations with date/time columns. The new Power Query updates allow these options to be viewed directly via the Date and Time menus, beneath the Transform and Add Column tabs. The option to disable column name prefixes in the Aggregate Columns menu also now exists
Big Data is all the rage now, and Microsoft is taking note. Solutions are focused on making sense out of data, to render it actionable and useful. The improvements in this latest update to Power Query follow a general trend of creating functionality to support unstructured data and Big Data technologies. As a front-end, Excel offers some powerful capabilities for visualizing and charting information. With the aforementioned performance improvements, loading massive data sets into Excel is now more manageable. Some non-traditional data sources supported by Excel’s Power Query include MySQL, Azure Database, DB2, Teradata, Hadoop File System (HDFS), and Facebook, among others.
In short, the latest updates to Power Query further position Excel as a powerful component in the business intelligence toolset. With the right expertise, one can gain powerful insights into data for making informed business decisions. To this end, we’re here to offer help and guidance with Excel programming and use. Please contact us for more information.
