Excel 2016 includes a powerful new set of features called Get & Transform, which provides fast, easy data gathering and shaping capabilities. These updates are available as part of an Office 365 subscription. 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. This update includes the following new or improved Data Transformation features:
Filter by “not earliest/ latest date”
Microsoft has added a new Date/Time columns filter option in the Query Editor to allow users to filter by dates that are not the earliest or latest dates within the current column.
Filter by “is in previous N minutes/hours/seconds”
Another new Date/Time filter provides the ability to filter out values that are within the previous N minutes/hours/seconds. This can be accessed from the In the Previous… option under the Filter menu for Date/Time columns. You can then define the filtering options by specifying the value and the desired filtering scope from the drop-down. Note the new hours, minutes and seconds options added in this update.
Copy and paste queries between Power BI Desktop and Excel
We know that users often work with multiple tools in their daily activities and use Excel alongside Power BI Desktop for dash boarding and sharing scenarios. In a previous update, the ability to copy and paste queries between different Excel workbooks was enabled. This update makes it seamless for users to copy and paste their queries between Excel and Power BI Desktop. Users can now right-click a query or a query group in the Queries task pane in Excel workbook to copy those queries. They can then paste them into the Queries task pane in Query Editor for Power BI Desktop. Copying the queries from the Power BI Desktop into Excel is done in a similar way.
Support for Special Characters in Split Column
Previously, we added support for using Special Characters in the Replace Values dialog to find and replace values. In this update, we enabled the same functionality for Split Column by Delimiter operations.
Refresh previews in Merge Queries dialog
It is now possible to refresh the table previews within the Merge Queries dialog so users can select which columns to match using the latest available data.
Monospaced font for Query Editor Preview
Users can now customize the Query Editor Preview to display content using a monospaced font. This customization option is found under the View tab.
Improved function invocation experience
It is now possible to invoke functions directly from within the Navigator dialog. This allows users to invoke and preview results of function invocation against a data source (such as SQL Server).
Users can now specify the function parameters directly from the Navigator window
Option to set credentials at the server level
Users are now given the chance to set the scope for their credentials when prompted for Database credentials. This is available as a radio button option at the bottom of the Credentials dialog.
Add prefix/suffix to a Text column
It is now possible to add a prefix/suffix to an existing Text column via Query Editor—either by using this option under Transform (modify column in place) or under Add Column to create a new column (available under Text Column >Format).
Source: Microsoft