Issue No. 56Mar 2017

New Updates to Microsoft Excel 2016

 

For those of you who use Microsoft Excel on a regular basis (or have staff that work with Excel frequently), we wanted to share some updates that Microsoft has recently added.  These updates are available in Microsoft Excel through Office 365.

Updates include the following connectivity and transformation features:

• New OLE DB connector.
• Enhanced “Combine Binaries” experience when importing from any folder.
• Maximize/Restore buttons in the Navigator and Query Dependencies dialogs.
• Support for percentage data type.
• Improved “Function Authoring” experience.

Improved performance for OData connector
One of the most popular scenarios in Excel consists of leveraging one of the folder-like connectors (such as Folder, SharePoint folder, etc.) to combine multiple files with the same schema into a single logical table. Before this release, users could combine Text or CSV files only. The combine would not work for any other supported file formats. With this release, Microsoft enhanced the “Combine Binaries” experience when importing from any folder.

With this new approach, users can easily combine all binaries within a folder if they have a homogeneous file type and column structure. Users can also easily apply additional transformations by modifying the “exemplar query” without having to worry about any additional function invocation steps, as they’re automatically generated for them.

Maximize/Restore buttons in the Navigator and Query Dependencies dialogs

The Navigator and Query Dependencies dialog (activated from Query Editor) support window resizing by dragging the bottom-right edges of the dialog. In this release, we made it possible to maximize/restore these dialog by exposing Maximize and Restore icons in the top-right corner of the dialog.

Support for percentage data type

With this update, Microsoft added support for percentage data types, so they can easily be used in arithmetical operations for Get & Transform scenarios. An input value such as “5%” will be automatically recognized as a percentage value and converted to a two-digit precision decimal number (i.e., 0.05), which can then be used in arithmetical operations within a spreadsheet, the Query Editor or the Data Model.

Besides automatic type recognition from non-structured sources (such as Text, CSV or HTML), users can also convert any value to percentage using the Change Type options in the Query Editor.

Improved “Function Authoring” experience

Microsoft also made it easier to update function definitions without the need to maintain the underlying M code.

Here’s how it works: Create a function based upon another query using the “Create Function” command. You do this by right-clicking the Queries pane inside Query Editor. When you do that, a link will be created between the original query and the newly generated function. This way, when the user modifies the original query steps, the linked function will be automatically updated as well.

Improved performance for OData connector
With this update, Microsoft added support for pushing Expand Record operations to be performed in the underlying OData service. This will result in improved performance when expanding records from an OData feed.