Excel 2016 includes a new set of features based on Power Query technology that can be accessed through the Get & Transform section on the Data ribbon, in praticular:
- new or improved data transformation features
- new or improved data connectivity features
- Remove blanks via Column Filter menu
- Convert Duration values to Years: can be found under the Transform or Add Column tabs.The logic applied is to divide the total number of days by 365.
- Keep Duplicates: This new option can be found on the ribbon under Home > Remove Duplicates split button and then select the Keep Duplicates command.
- Hints for “sample input values” in the “Change Type with Locale” dialog:Power Query allows users to change the type of a column taking into account the Locale or regional settings/formats in which the data is being represented. This can be done by setting the Locale value for the entire workbook (under Data > New Query > Query Options > Current Workbook > Data Load) and also can be customized for each specific “Change Type” step in the queries.
- Support for whitespace and line feeds in Query Editor preview:You can toggle between “Show whitespace” (default behavior) and not showing it (old behavior) from the View tab on the Query Editor ribbon.
- Ability to disable previews from the Navigator window: allows users to reduce the number of calls being made to the data source in order to retrieve these previews.
- Technical name support in the Navigator window.
- Rename queries directly from Queries pane in the Query Editor: it is now possible to rename queries directly from the Queries pane inside the Query Editor. To rename a query from this pane, simply select and right-click the query and select Rename, or double-click the query name, or select it and then press F2.
- Support for Command Timeout in the UX: users are now able to specify a Command Timeout value (in minutes) when connecting to database sources. This new Command Timeout option can be found under the “Advanced options” section in data source dialogs.
- Set to disable Privacy Level prompts at machine level (including Registry Key). Power Query allows users to combine data from multiple data sources into a single workbook. When dealing with multiple data sources, it is possible for users to define queries that require sending data from one data source to another data source.This release allow users and enterprises to pick one of the following behaviors:
- Always combine data according to your Privacy Level settings for each source—This new option allows a user to “enforce” that Privacy Levels are taken into account for every .XLSX file on their machine, regardless of whether the “Current Workbook” Privacy Level setting is enabled or disabled.
- Combine data according to each file’s Privacy Level settings—This is the default behavior and matches the Power Query behavior in previous releases.
- Always ignore Privacy Level settings—This new option allows a user to always bypass Privacy Level settings for every .XLSX file in their machine, regardless of the Current Workbook setting.