July 2016 Updates for Microsoft Excel® in Office 365

July 2016 Updates for Microsoft Excel

Excel’s Power Query technology provides fast and easy data gathering and manipulation capabilities, ideal for calculations and reporting based on databases. Accessed through the Get & Transform section on the data ribbon.

Microsoft have now released 12 new data transformation and connectivity features all available as part of the Office 365 regular updates. Even if you are using older versions of Excel, ie Excel 2010 or Excel 2013 you can take advantage of the updates by downloading the latest Power Query for Excel add in.

The New Excel Power Query Connectivity and Transform Features

The following are a list of the new connectors and transformation features that Microsoft have released for Excel 2016:

• New SAP HANA connector.
• New SharePoint Folder connector.
• New Online Services connectors category.
• Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2.
• Improved Text/CSV connector, now exposing editable settings in the preview dialog.
• Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy.
• Data Source Settings enhancements, including “Change Source” capability.
• Advanced Filter Rows dialog mode within the Query Editor.
• Inline Input controls for Function invocation within the Query Editor.
• Support for reordering Query Steps within the Query Editor by using drag and drop gestures.
• Date picker support for input Date values in Filter Rows and Conditional Columns dialogs.
• New context menu entry to create new queries from the Queries pane within the Query Editor.

New SAP HANA Connector

In this update, we added a new connector to allow users to import data from SAP HANA databases. The new connector can be found under Data > New Query > Database category, or from the Query Editor window.

New SharePoint Folder Connector

Available under Data > New Query > File category, the new SharePoint Folder connector enables users to import data from multiple files within a SharePoint folder, similar to how the existing Folder connector enables users to import and combine multiple files from a file system folder.

New Online Services Connectors Category

With this update, we added a new category that includes all available connectors for Online Services in Excel. The new category is available under Data > New Query > Online Services or from the Query Editor window.

Improved DB2 Connector, now leveraging the Microsoft driver for IBM DB2

In this release, we improved the IBM DB2 connector to provide users the choice to leverage the Microsoft driver for IBM DB2, which is also automatically included in the Excel/Power Query installation.

Within the IBM DB2 connector dialog, users can now select which driver to use under Advanced options.

Improved Text/CSV Connector, now exposing editable settings in the preview dialog

In this update, we improved the Text and CSV connectors so users can configure basic import settings from within the preview dialog. These import settings include:

File Origin.
Delimiter.
Detect Data Type (strategies include: Base on top 200 rows, Base on entire dataset or No data type detection).
Based on user choices, the preview in this dialog automatically updates. Users can then decide whether to directly load the data or edit first to apply additional data transformations.

 

Improved relational database Connectors, now including Schema information as a part of the Navigation hierarchy

In this update, we enhanced the database connectors to expose an option that allows users to include Schema information as part of the Navigation hierarchy. This option is available under the Advanced option in all database connector dialogs, such as the SQL Server Database dialog in the image.

 

Data Source Settings enhancements, including “Change Source” capability

Users can now easily update the location of a data source for all queries connected to that data source within a single Excel workbook. Before this update, users had to update the data source location for each of the queries connected to the source. The new solution allows users to do this in a single step.

Within the Data Source Settings dialog (Data > New Query > Data Source Settings), there is a new Current Workbook scope option, which allows users to limit the list of displayed data sources to only those that are used in the current file. From this view, the users can modify credentials, privacy levels and other settings for that data source, or use the Change Source option to modify the location for that data source.

 

Advanced Filter Rows dialog mode within the Query Editor

We’re introducing a new mode in the Filter Rows dialog within the Query Editor. This new mode allows users to add multiple filter clauses within a single Filter Rows step (before this update, only 1–2 clauses were allowed) and also combine filter clauses based on multiple columns (before, all clauses were applied to a single column).

 

Inline Input controls for Function invocation within the Query Editor

We improved the inline preview for functions within the Query Editor dialog to allow input values for function invocation. This new Enter Parameters section in the inline function preview is available for both “out of the box” functions (M Engine functions) as well as user-defined functions.

 

Support for reordering Query Steps within the Query Editor by using drag and drop gestures

We now support reordering of Query Steps within the Query Editor by using drag and drop gestures. This can be done for each query via the Applied Steps section in the Query Editor.

 

Date picker support for input Date values in Filter Rows and Conditional Columns dialogs

In this update, users can now leverage a Date picker control to provide input Date values in the Filter Rows and Conditional Columns dialogs.

 

New context menu entry to create new queries from the Queries pane within the Query Editor

You can now add a new query in the Queries pane within the Query Editor when you right-click in the background of the pane or in a query group node. This allows a more intuitive experience for adding new queries when working in the Query Editor pane. Also, when you right-click a query group, the new query is added to the selected group.

 

Find Out More

Why not learn more at one of our popular training sessions:

Check out the scheduled training >

Alternatively, join us at the Business Software Showcase event at our Hampshire offices, to find out all of the latest in software to manage your company:

Register for the Business Software Showcase >