July 2018 update for Get & Transform in Excel 2016 and the Power Query add-in
Published Jul 16 2018 07:29 AM 14.6K Views
Microsoft

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.

Column From Examples.png

  • 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.

 Always Use Connection File.png

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.

OLEDB.png

 

 

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.

SAP Hana.png

 

Guy Hunkin

— Excel Team

12 Comments

Thank you Guy. Have no idea about SAP HANA, all the rest looks quite useful. 

Bronze Contributor

When will we get the new Web page connector that Power BI got in beta in June (or May?)

Microsoft

@Ed Hansberry, the rule of thumb in Excel is to expose only those connectors that reached a general availability quality. The new Web page connector is still in Beta and is not there yet. I don't have any estimations for you at this stage, but please let's sync in a couple of weeks from now.

 

Guy Hunkin

— Excel Team

Great work on the continuous improvement of Power Query.  I know a lot of business users that will benefit massively from the Columns From Examples capabilities

Bronze Contributor

Thanks @Guy Hunkin. Maybe voice to the rest of the Excel team to allow for "beta connectors" to be enabled in Excel as well. Something to think about after Office 2019 is launched. 


 I just added this to Excel UserVoice

Copper Contributor

That enhancement to G&T is another winner. I just tried it and it's a very useful addition!

 

Duncan

Copper Contributor

"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."

 

This is precisely what I'm looking to do - import an ODC query connection into another workbook. Unfortunately, ODC files are the one data source that Power Query in Excel does not list as an option to pull from. I have an ODC files exported and waiting to refer to. How do I get Power Query to connect to it in the new workbook where I actually want it?

Copper Contributor

Please disregard my question above. I need to solve the root of my issue rather than grasp at work-arounds. For days I've been tying to connect to an Excel file (any Excel file at this point) using Power Query but all I get is an endless "Please wait while we establish a connection to ..." Never encountered anything like this. Extremely frustrating. Please do let me know if you've ever heard of such a thing and if it was ever resolved. 

Microsoft

@Steven Weydert , please please submit a feedback via File > Feedback > I Don't Like Something next time you encounter this error.

Copper Contributor

My company has Microsoft Office 2016 installed via MSI rather than Click-to-Install. Can you confirm which KB would need to be deployed. to update this to 2.62

Microsoft

@MichaelLambAU , this changes are available for Office 365 subscribers only.

Copper Contributor

Hola mi empresa tiene Office 365, sin embargo no tengo la totalidad de los conectores de Get & Transform, estoy interesado en el de Mysql data source.  Mi versión de excel es 1910 y la versión de power query es 2.74.5619.262 de 64 bits. Como puedo tener  estos conectores? ya he desintalado e reinstalado un par de veces el aplicativo usando el Microsoft support. 

 

 

Version history
Last update:
‎Jul 16 2018 07:29 AM
Updated by: