Body:
This month’s Power Query update includes 11 new or improved features including:
- Support for all Excel 2013 Desktop SKUs
- OData V4 support
- Unified Options dialog
- Option to disable the Native Database Queries prompt
- Support for custom ADFS Authentication Services
- Updated Facebook connector due to Facebook API changes
- Support for Fixed Decimal Number type
- Alternate Windows Credentials
- Online Search is now Data Catalog Search and in a new ribbon location
- New transformations
- Additional performance improvements for loading medium and large datasets.
Continue reading below for more details about each feature.
Support for all Excel 2013 Desktop SKUs
With this update, we’re making Power Query available to all Excel 2013 Desktop SKUs. There are some differences in features, depending on what SKU users are running:
- Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone: Full Power Query feature set.
- All other desktop SKUs—Full Power Query feature set, except the following data connectors: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects and Salesforce.
Power Query detects your Excel 2013 SKU when launched and enables the appropriate set of features.
OData V4 support
We’re adding support for OData V4 feeds. You can just use the existing OData Feed connector, which will now also accept feeds built using the latest OData version.
Unified Options dialog
We have combined Workbook Settings and Options into a single dialog, organized by scope (current file versus global) and categories for easier navigation.
Option to disable the Native Database Queries prompt
One useful capability when connecting to databases is the ability to provide a custom SQL statement. This is helpful for customers who have complex SQL queries to pull data for their reports and want to get started with Power Query. However, there is a potential risk for these queries to contain malicious SQL code that could delete or modify content in a database when executed. Because of this risk, we have an existing security prompt whenever users try to run a native database query outside of the data source dialogs. It turns out that lots of customers are using this capability within Custom Columns or similar scenarios, so they can dynamically build and execute native database queries. The downside is that they would get prompted for approval of every distinct SQL statement, which would make the experience very inconvenient.
To enable these customers to achieve their scenarios, we’re introducing an option to disable Native Database Query security prompts. However, please beware of the potential risks mentioned above before using. You can find this option within the Options dialog, under Global > Security.
Support for custom ADFS Authentication Services
With this update, we added support for using custom ADFS authentication endpoints through our Organizational Account credential type. This allows access to data sources that require ADFS authentication such as some on-premises instances of Dynamics CRM. After Power Query is registered by your admin, you will be able to approve a custom endpoint when prompted for access. You can also manage the list of already-approved endpoints within the Options dialog, under Global > Security.
Updated Facebook connector
As of April 30th 2015, Facebook expired v1.0 of its Graph API. The Graph API is what Power Query uses behind the scenes for the Facebook connector, allowing you to connect to your data and analyze it. This expiration means some changes in the Facebook connector as it currently exists. The most important difference is the set of permissions we’re able to leverage and the data those permissions return. For example, Friends Lists and News Feeds are commonly used permissions that are now changed or inaccessible.
Queries built before April 30th 2015 may no longer work or return less data. After April 30th, Power Query leverages v2.2 in all calls to the Facebook API. You’ll likely need to re-authenticate to approve the new set of permissions. More details on the change in the Facebook API are available here.
Support for Fixed Decimal Number type
We have added support for Fixed Decimal Number type. This new type can be found in the Data Type drop-down menu under Home and Transform tabs in the Query Editor, as well as in the Change Type column context menu.
Alternate Windows Credentials
Added an option to use Alternate Windows Credentials (rather than current user) to the Windows credentials option in the Credentials dialog.
Online Search is now Data Catalog Search and in a new ribbon location
We renamed Online Search to Data Catalog Search and moved it from the Get External Data group to the Power BI group on the Power Query ribbon tab.
New transformations
We continue making incremental improvements to the set of transformations supported in the Query Editor. This month, we added the following new transformations:
- Remove Blank Rows.
- Median Operation available for Group By and Aggregate Column.
- Convert DateTimeZone value to Local Time.
Performance improvements
In addition to all the functional improvements described above, we also made Power Query faster when loading medium and large datasets into your Excel Workbook. Your queries will take approximately 20 percent less time to load than what they used to take with last month’s update.
That’s all for this month. As mentioned previously, we’re making lots of incremental improvements to Power Query and we hope that you find it better with every new monthly update. Please continue sending us feedback using our “Send a Smile/Frown” feature, or by voting for what you’d like to see next.
—Miguel Llopis, program manager on the Power Query team
———————
Power Query for Excel is available with an Office 365 subscription, Office 2010 Professional Plus with Software Assurance, Office 2013 Desktop SKUs or Excel 2013 Standalone. Download the add-in and learn more about getting started.
Learn about all the powerful analytics and visualization features in Excel and take your analysis further by sharing and collaborating on business insights with colleagues using Power BI.
- Learn more about Power Query
- See all analytics and visualization features in Excel
- Get your Office 365 subscription
- Try Power BI
