Today, we released another set of updates for Get & Transform — a powerful set of Excel 2016 features based on the Power Query technology, which provide fast, easy data gathering and shaping capabilities.
These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get the latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in.
The following new features and improvements have been introduced in this release:
Add Column From Examples enhancements
Add Column From Examples, which can be accessed via Add Column tab on the Query Editor ribbon, enables users to easily add new columns that derive data from existing columns by simply providing one or more sample values for your new column. Query Editor then automatically identifies and applies the required transformations for the new column.
This month we made significant enhancements to the Add Column From Examples feature:
- Composition of data transformations - Users can now derive new columns from examples that require the composition of multiple column transformations. In the following example, we show how you can extract the name initials followed by the occupation in upper-case in parenthesis from the Full Name and Occupation columns.
- Domain specific transformations - We enhanced the set of supported data transformations even further by including specialized, domain-specific transformations, such as additional Date extractions (e.g., 5/8/2018 -> MAY-2018) and more.
Always Use Connection File support
A popular scenario in data access domain in Excel is the ability to share queries. Today you can export your query definitions into an Office Database Connection (ODC) file, then share it across your workbooks or with your fellow co-workers. Once you receive an ODC file, you can consume it and import the query definition into your workbook.
With this update, you can force the query to always use the most up-to-date definition as stored in the ODC file whenever the data is displayed or refreshed. Right-click on a query which is linked to an ODC file in the Queries & Connections side pane, choose Properties, go to Definition tab and select the "Always use connection file" check box. Clear this check box to use the query definition as stored in the Excel workbook.
Note: If the ODC file is not available, Excel resorts to the query definition that is saved in the workbook. If you want to ensure that the most up-to-date version of the query is always used, make sure that the ODC file is accessible and available.
ODBC Connector enhancements
With this update, we introduced the following improvements to the ODBC connector in Excel:
- “Keep top rows” transformation command will be pushed down to the ODBC driver, which may improve performance of the connector if the driver and underlying data source support the “top” operator.
- If the DSN or connection string specified in the ODBC connector dialog includes a DSN catalog, Excel will narrow down the list of tables exposed in the Navigator dialog accordingly.
Support for alternate Windows credentials in OLEDB connector
In this release, we enhanced the authentication options for the OLEDB connector and added the ability to provide alternate user credentials when using Windows authentication.
OData V4 Connector enhancements
We made several important improvements to our OData connector in order to provide richer support for OData V4:
- Data load and refresh will be significantly faster specially when the feed contains complex types.
- Greater resiliency - Smarter query folding logic increases the query success rate.
- Improved support for complex types and open type navigation columns.
- Improved support for custom URLs - When the user specifies OData query options manually, Excel will adjusts the type of the imported table according to the response.
Port selection in SAP HANA connector
With this update of the SAP HANA connector, users can explicitly select the port to use when connecting to a SAP HANA database. In previous releases, Port could be specified as part of the Server input field. However, the default port when not specified would not distinguish between single-container and multi-container SAP HANA servers.
With the introduction of this feature, users can now specify what type of SAP HANA server they are connecting to and get an optimized port default for it, or alternatively select the Custom option to specify a different port number.
Guy Hunkin
— Excel Team
Subscribe to the Excel Blog to get the latest product announcements and updates