More April 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Excel 2016 includes a powerful new set of features based on Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce more updates to this powerful feature set. These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these 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.

These updates include the following new or improved data transformation features:

• Ability to append multiple tables within a single Append operation.
• Alphabetical sort of columns in Choose Columns builder.
• Improved performance of renaming/removing/reordering columns.
• One-click Percentage transform.

The following new or improved Data Connectivity features:

• Support for jagged CSV files.
• Exchange connector—OAuth support.
• SharePoint List Connector—URL validation.
• Database Connectors—ability to disable Navigation Columns in Query previews (performance optimization).
• Web Connector—fine-grained scoping of Credentials.

And several other important improvements:

• Virtualized preview in Query Editor.
• Option to disable data previews to download in the background (performance optimization).

Ability to append multiple tables within a single Append operation

Users can now easily append more than two tables within a single Append operation. In the Append tables dialog, users can switch between “Two tables” and “Three or more tables” modes.

Two tables mode (previous behavior):

Three or more tables mode:

The new Three or more tables mode allows users to be much more efficient when trying to append several tables together. Before this option, users had to append tables one by one as different steps (or modify the underlying generated M formula).

While the Three or more tables mode is extremely powerful for advanced scenarios, we also want to ensure the simple scenario of appending two tables remains as easy as it used to be, so the default mode for the Append dialog is still Two tables. Users can switch between these two modes by using the radio-buttons at the bottom left corner of the Append dialog.

Alphabetical sort of columns in Choose Columns builder

The “Choose Columns” transformation builder has been improved to allow users to sort the list of available columns either alphabetically or based on natural order (previous behavior). The default sort continues to be Natural Order, that is, the order in which the columns appear in the table.

Improved performance of renaming/removing/reordering columns

We improved the performance of common transformation operations within the Query Editor (Rename Columns, Remove Columns and Reorder Columns) to provide a better user experience. These operations are now performed locally, rather than requiring a reload of the data from the remote data source when applied.

One-click Percentage transform

Users can now easily calculate a percentage amount based on the value in a different column. In order to do this, users had to type Custom Column expressions in previous releases.

With this update, users can simply select the original column and then click Add Column > Standard > Percentage on the Query Editor ribbon. You are asked for the Percentage value to apply to the original column and then will get a new column as the output containing the result of this calculation.

Support for jagged CSV files

We have improved the CSV file connector to automatically detect jagged CSVs. Jagged CSV files are characterized by having a different number of columns across rows, generally with more columns showing up after the first few rows with less columns.

With this update, the detection logic for data columns in CSV files has been dramatically improved to recognize these extra columns. There’s no additional user action required to benefit from this improvement (besides importing the CSV file again using the latest product version).

Exchange connector—OAuth support

We improved the Exchange connector and added the support for authenticating using a Microsoft account (organizational account) when using the Exchange connector.

SharePoint List connector—URL validation

We’re improving the SharePoint List connector by performing validation of the URL provided by the user to connect to their SharePoint List.

Users can provide the URL to their SharePoint Site root and Excel will detect all SharePoint lists available within the site and present the user with a Navigator dialog displaying all available lists.

With this update, when users provide an invalid URL, they are notified about this issue and won’t be able to move forward. Before this update, users would get a subsequent error due to using an invalid URL, which was not indicative of the actual issue.

Database connectors—ability to disable Navigation Columns in Query previews (performance optimization)

Users can now disable Navigation Columns when connecting to databases. This option allows a big performance optimization in retrieving data previews from databases when Navigation Columns are disabled.

Navigation Columns are extremely useful when trying to expand or aggregate fields from a related table in a database. For instance, from a Customers table that has relationships to an Orders table, users could expand fields from the Orders table to get the list of all Orders from all customers. Similarly, they could also perform aggregate operations over fields from the Orders table (such as the Sum of Total Order Prize). This would allow them to easily combine data from multiple related tables by just leveraging the existing relationships in the database and the Get & Transform UI options in Excel (Expand and Aggregate).

However, when not interested in this type of data transformation, it turns out that the cost of retrieving Navigation Columns for a given table is high enough that it degrades the performance of retrieving data previews, thus degrading the overall user experience for simple scenarios.

With this update, we introduced a new option in the Source dialog for database connectors to disable importing these relationships or navigational columns. Please note that this option is available for all relational database connectors except for SQL Server, Azure SQL Database and SQL Server Analysis Services.

Web Connector—fine-grained scoping of Credentials

We improved the Credentials prompt when connecting to Web data sources to allow users to specify the scope to which they would like the credentials to apply. Users can now pick any of the levels within the URL (domain, specific page and all levels in between).

Virtualized preview in Query Editor

We improved the Query Editor data preview so that it can display as many columns and rows as exist in the table. Before this update, there were a set of restrictions such as a maximum number of cells displayed in the preview (3,000) and a maximum number of columns (100).

With this update, we removed these limitations and users will be able to scroll in the table preview to see the entire set of contents.

Option to disable data previews to download in the background (performance optimization)

We added a new workbook-level option to disable downloading data previews in the background. Changing the default behavior would reduce the amount of data being processed in the background by Excel, which may be suitable for users who do not need to preview queries locally.

This new option can be found on the Data ribbon > New Query > Query Options > Data Load tab under the Current Workbook section.

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these 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.

• Learn more about what’s new in Excel 2016.
• Join our Excel community on Facebook and Twitter.

—The Excel team