Just a few days ago, Microsoft announced three new data transformation and connectivity features that have been requested by many customers.

Excel 2016 includes a powerful new set of features based on the 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.

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 last Power Query FOR Excel add-in.

There updates include the following new or improved data connectivity and transformation features:

• Query Parameters support.
• Improved Web connector—web page previews.
• Query Editor improvements—option to Merge/Append as new query.

Query Parameters Support

With this update, users can now create and manage parameters for their queries within the Excel workbook. The new “Manage Parameters” dialog is available on the ribbon under the Home tab within the Query editor.

The new dialog allows the users to create new parameters, give them a meaningful Name and Description, specify the information about the expected parameter type and values, default value and current value.

Once one or more parameters are available in the current workbook, users can reference those parameters in their queries via Query Editor. Referencing parameters is supported via the Data Source dialogs, Filter Rows, Keep Rows (top/bottom, etc.), Remove Rows (top/bottom, etc.), Replace Values, Add Conditional Columns dialog and more.

In addition, parameters can be loaded to the grid or to the Data Model just like any other query, allowing references from Excel formulas or DAX measures.

Improved Web connector—web page previews

One of the most unique Get & Transform connectors is the Web connector. With the Web connector, users can easily import data from websites that has been formatted as an HTML table or even pull data from Web APIs.

When using the Web connector for “scrapping” data from HTML pages, a very common challenge is that the Navigator view, which is based on a list of tables, is not very helpful in identifying the desired tables. This is particularly hard when dealing with web pages that contain lots of tables, and in many cases, with not very representative table names.

With this update, Microsoft is introducing a new mode in the Navigator dialog that allows users to preview tables on the web pages “in context” and select the desired tables by just clicking on them within the Web View preview. This results in a much more intuitive and seamless user experience for selecting tables from a web page.

To access this mode, click the Web View button at the top of the Navigator dialog. Users can also switch back to the classic data-centric view by selecting the Table View option.

Query Editor improvement—option to Merge/Append as new query

Within the Query Editor, users can easily merge (join) or append (union) multiple tables, allowing them to mash up data from multiple sources into a single table. The Merge/Append operations are on the ribbon under the Home tab inside Query Editor.

In previous versions of the Query Editor, Merge/Append operations were always applied as new steps within the current query. Starting with this update, users can decide whether to apply these operations as a new step in the current query (old behavior) or whether the output of the Merge/Append operation should be created as a new query (new behavior).

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. If you have questions about these updates or need help getting the latest updates, contact us at sales@vorsite.com or (206) 781-1797.