Thursday, April 2nd, 2015 / by ExcelHelp
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.
– 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.
– 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.
– 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.
I can not recommend ExcelHelp more highly. We had been struggling to complete a critical project and finding them was a lifesaver. Tom was knowledgable, professional, personable, patient and responsive. Look no further for your database programming needs. Thanks Tom and ExcelHelp.com!Randy Hartnell
We anticipate a long business partnership with ExcelHelp and look forward to working with them. I have been nothing but impressed with your firm’s services, your team members tenacity and the overall results. Sometimes you have to travel a bumpy road trying different firms until you find the one that fits.Posted By: Charlene Faber
Chief Operation Officer, Current Builders
I’d like to express my gratitude for the help and cooperation we’ve received from your entire organization during the development of our Modeling Utility. Over the past several weeks we’ve engaged key customers worldwide...We have confidence that this effort will add significant capability and value to the overall success of the program.Business Development Manager
Network Architecture and Strategy
Posted By: Marty M.