Excel 2016's Get & Transform & Power Query Boost Data Transformation, Connectivity, More

May 2017 update for Get & Transform, Power Query add-in, Transform Data By Example add-in for Excel 2016, redesigned OneNote with a consistent experience across devices.

Share online:

Excel 2016 Split Column by Delimiter

Three new data transformation and connectivity features are now available for the Get & Transform section and the Power Query add-in for Excel 2016 as on Thrusday.

The updates include automatic detection and population of the delimiter field in Query Editor, the ability to use sample files from folders to select objects and specify custom transformation steps in the Combine Files experience, and the option to specify the package collection to connect to within a DB2 server.

These updates are available as part of an Office 365 subscription. Excel 2010 or Excel 2013, can get these updates by manually downloading the latest Power Query for Excel add-in.

When splitting column by delimiter inside Query Editor via Home -> Split Column, Excel will automatically detect and populate Delimiter field in this dialog, based on text pattern detection in the data preview rows. Note that you can still change the delimiter if needed, but this automatic detection saves a few clicks in many cases.

Combine Files in Excel 2016
Combine Files

Combine Files adds an ability to select a file from a folder to use as the sample file to select objects and specify any custom transformation steps (if needed). With this release, another new control added in file preview dialog allow choosing a file available in the selected folder. The default selection is the first file found—however, this can now be easily customized by users.

DB2 connector window now includes an option to specify package collection to connect to within a given DB2 server. This new option, available under the Advanced Options section in the DB2 connector dialog, can only be used with the Microsoft IBM DB2 driver.

Excel 2016 DB2 connector
DB2 connector

A new Microsoft Garage Excel add-in 'Transform Data by Example,' helps automatically find relevant data transformation functions and compose them to solve data wrangling tasks. With this add-in, users provide a few examples of desired output, and clicking on Get Transformations and Transform Data by Example will return a ranked list of programs that are automatically generated using a large collection of transformation functions indexed to perform desired transformation tasks. When users click on a result, "the add-in applies that program and fills the output column with the transformed data."

In addition through an easy extensibility interface, users can add their own data transformation code to collection as shown below. Transform Data By Example makes the new transformation functionality instantly searchable.

Excel 2016 Transform Data By Example add-in
Transform Data By Example

Like a web search engine, Transform Data By Example continuously improves the more you use it, the better it gets.

Excel 2016 Extensible engine for transformations
Extensible engine for transformations

In an update to the look & feel of OneNote for Windows 10, Mac, iOS, Android and on the web, the following features are rolling out over the coming weeks, with focus on three key areas such as:

Enhanced usability for those with disabilities—such as vision and mobility impairments lets more easily use OneNote with keyboard shortcuts.

Simplified navigation places all controls on the left side of the app, so users can easily switch between their notes and dramatically improves usability with assistive technologies.

This consolidated and simplified design let screen readers easily navigate through the app to help those with disabilities.

In addition, students can now focus and avoid distractions, as content is front and center.

Consistency across devices—allowing users to capture thoughts, jot down notes and get things done more quickly. With this update, students will now be able to easily transition between their home and school devices.

OneNote consistent experience across devices

Check out the videos below or this help article to learn more about the specifics of OneNote's update:

Microsoft releases new Outlook for Mac Insider Preview to Fast ring, bringing the version 15.35 (170517), includes a range of bug fixes to help improve the overall user experience:

  • We no longer display an attachment icon in the message list for messages with inline images
  • When deleting a conversation using the hover button in the message list, the selection goes to the next conversation
  • For client certificate authentication, your email address must now match the email address of the certificate
  • You can now use tab key to navigate to the Security & Privacy preference in Outlook preferences
  • Drop-down list buttons in the event editor, including Duration and Time zones, now work properly