EXCEL 2016 QUERY EDITOR MANAGE PARAMETERS HOW TO
If you are an Office 365 subscriber, find out how to get these latest updates.
Updates outlined in this blog are available as part of an Office 365 subscription. 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).Įxcel 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. In previous versions of the Query Editor, Merge/Append operations were always applied as new steps within the current query. The Merge/Append operations are on the ribbon under the Home tab inside Query Editor. 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. Query Editor improvement-option to Merge/Append as new query Users can also switch back to the classic data-centric view by selecting the Table View option. To access this mode, click the Web View button at the top of the Navigator dialog. This results in a much more intuitive and seamless user experience for selecting tables from a web page. With this update, we’re 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 is particularly hard when dealing with web pages that contain lots of tables, and in many cases, with not very representative table names. 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. 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. One of the most unique Get & Transform connectors is the Web connector. The in-depth tutorial on query parameters will be coming on the Excel blogs portal soon. 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. 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. Once one or more parameters are available in the current workbook, users can reference those parameters in their queries via 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. The new “Manage Parameters” dialog is available on the ribbon under the Home tab within the Query Editor.
With this update, users can now create and manage parameters for their queries within the Excel workbook.
Today, we are pleased to announce 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.