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.
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.
Enhanced Active Directory connector credentials
The Credentials dialog for Active Directory now allows users to select alternate Windows credentials.
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.
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.
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.
- Learn more about Power Query
- See all analytics and visualization features in Excel
- Get your Office 365 subscription
- Try Power BI
- Follow us on Facebook and Twitter