Back
Excel

30 feature updates to “Get & Transform” in Excel 2016 and Power Query add-in

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

This week we’re pleased to announce a new set of features for “Get & Transform” in Excel 2016 and the Power Query add-in for Excel 2013/2010. This update is packed with lots of new features that have been requested by many customers. We expect that you will find this update extremely useful.

How can I get this update?

For Excel 2016: Install the latest Click-to-Run update for Excel 2016.

For Excel 2013/2010: Download and install the Power Query add-in for Excel 2013 and Excel 2010.

Data connectivity

Updates to data connectivity include:

  • Support for SharePoint Lists from non-English sites.
  • Enhanced support for the Exchange connector.
  • Automatic column type detection when importing .XLS files via the Excel Workbook connector.
  • A new “Select Related Tables” option when connecting to database sources.
  • Enhanced Active Directory connector credentials.
  • An improved function invocation experience.
  • A new option to delete all entries in the Data Source Settings dialog.
  • An option to “Enable Relationship Import during Refresh operations.”

You can continue reading below for more details about each feature.

Support for SharePoint Lists from non-English sites

The SharePoint Lists connector (under “Other Sources”) allows users to import data from lists in their SharePoint sites. However, there used to be a limitation that only sites where the Site Language was set to English would work with our connector. We improved the connector in this release to remove this restriction, so now users can get data from any SharePoint List in any site (SharePoint 2013 or newer) regardless of the site language. Note that SharePoint 2010 sites continue to work but still expose the same language limitation, as this is a change available only in newer versions of the SharePoint API.

Enhanced support for the Exchange connector

We have improved the Exchange connector to allow connections to multiple mailboxes. Users can provide the email address to connect to as part of the Source dialog and then specify credentials for that source (which may or may not match the email address to connect to). This way, users can easily combine data from multiple Exchange mailboxes into a single report.

Automatic column type detection when importing .XLS files via the Excel Workbook connector

The Excel Workbook connector now performs automatic column type detection when importing .XLS files. Before, this type detection was done only when importing .XLSX files.

Select Related Tables option when connecting to database sources

Users often will import multiple tables when connecting to a database. Before this update, users had to manually select all tables that they wanted to import, which often required an understanding of the underlying database schema so they would pick all tables that are related. With this update, we’re adding a new button called Select Related Tables to the Navigator dialog. When users click this button, all tables that have a direct relationship to one or more of the already selected tables are automatically selected.

Excel 2016 and Power Query Nov update 1

Enhanced Active Directory connector credentials

The Credentials dialog for Active Directory now allows users to select alternate Windows credentials.

Excel 2016 and Power Query Nov update 2

Improved function invocation experience

The experience for invoking functions loaded from a data source has been improved in this update. For instance, users are now prompted for parameters when loading one or more functions from a data source (such as a database), when they click the Load or Edit button in the Navigator dialog.

New option to delete all entries in the Data Source Settings dialog

Users can now easily delete all stored Data Source Settings by clicking the new Delete All button.

Excel 2016 and Power Query Nov update 3

Option to “Enable Relationship Import during Refresh operations”

Excel automatically creates relationships between tables when loading them—if these relationships exist in the data source (such as a database). In addition to creating the relationships during load, Excel might create or delete relationships between tables while refreshing them if the relationships changed in the source. This used to be the default (and only) behavior before this update. With this update, we added an option under “Current Workbook—Data Load” to control whether to update relationship on refresh or not. The default behavior has been switched to not detect relationships on refresh, but still create them as part of the initial load.

Excel 2016 and Power Query Nov update 4

Data Transformations and Query Editor improvements

Improvements for Data Transformations and the Query Editor include the ability to:

  • Copy to clipboard (available for cells/columns/tables).
  • Filter date columns by earliest/latest date (dynamic filter).
  • Extract min/max date/time value from a column.
  • Replace values with a provision for specifying special characters.
  • “Detect Column Types” with an option to trigger type detection on demand.
  • “Refresh All Previews” to refresh all Query Editor previews with a single click.

Several performance improvements have also been included:

  • Choose Columns dialog—Faster user experience for dealing with wide tables.
  • Auto-filter and Expand/Aggregate popups—Faster for large number of values/fields.

In addition to these features, this update for “Get & Transform” in Excel 2016 includes all features released in the previous two Power Query updates. You can find more details in the following previous blog posts: Power Query August update and Power Query September update. You can read this blog post for information about “Get & Transform” (Power Query integration) in Excel 2016.

Power Query for Excel is available with Office 2013 or Office 2010 Professional Plus with Software Assurance. Download the add-in and learn more about getting started. You can receive update notifications in Power Query when there is a new version available. These notifications will show up in your PC’s system tray when you launch Excel. You can also check for updates by clicking the Update button on the Power Query ribbon tab.

That’s all for this month. As mentioned previously, we’re making lots of incremental improvements to “Get & Transform” in Excel 2016 and the Power Query add-in for Excel 2013/2010. We hope that you find these features 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.

___________________________________________________________________________________________

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.

Top

Join the conversation

10 comments
  1. Sorry – I might be a bit dense. Where is the “latest Click-to-Run update for Excel 2016” located? I have Office 2016 with Office 365.

    • Hi Ed. In order to update your Excel you need to go to: File – Account – Update Options drop down (on the right) – Update Now. Please let me know if it works.

  2. Great work guys! Is the update for Office 2016 live now? I am at version 16.0.6001.1038 but I don’t see any of the features mentioned in the post. Thanks.

  3. *sigh* with office 2013 my sys admins would let me install add-ins (after they reviewed them) and so I could keep Power Query up to date. Now, with Office 2016, since Power Query is coming through general office updates, I am going to have to wait until a service pack release comes out… they are blocking updates – on the Updates dialog it says “This product will not be updated. version 16.06001.1038”. I just opened a spreadsheet created in 2013 in excel 2016 only to be told that the sheet is using a newer version of power query and so could get broken in my version of excel.

    Is there not some way to grab these Power Query updates independently of the general office update process?

    • Right… so my laptop with Excel 2013 has Power Query 2.27, and my desktop with a new install of Excel 2016 version 1038 only gets Get & Transform/ Power Query version 2.24…. with no way to bring them back into sync apart from dropping my Excel 2013 Power Query back a few versions….

      So how the heck are we to know when we use Power Query whether it will be possible for others to use it? We now have to ask what version of Excel, what SKU, and what their subscription policy is? Nuts Microsoft, absolutely NUTS!

  4. It seems like the Power Query capability of Office 2016 ‘Get Data’ is not as up to date as the 2013 Plugin for Power Query.

    I am on the latest version for Excel 2016 and am an Enterprise O365 licenced user.

    When I try to merge two tables I only get the checkbox option to ‘only include matching rows’ but cannot select the join type. I could do this before in Excel 2013 with the Power Query Plugin…

    Is the only option to go to PowerBI for Desktop, build my query then copy and paste from the advanced editor into Excel 2016 Get Data Power Query module?

  5. I can’t see the “Select Related Tables” option when connecting to Access database.
    Is Access out of scope for this feature?
    Thanks

Comments are closed.