June 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in
Excel 2016 includes a powerful new set of features based on 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.
Today, we are pleased to announce four new data transformation and connectivity features that have been requested by many customers.
These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. 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.
These updates include the following new or improved features:
- Conditional Columns.
- Column type indicator in Query Editor preview column headers.
- Reorder Queries and Query Groups inside Query Editor via drag and drop gestures.
- Query Management menu in Query Editor.
Conditional Columns
With this update, we’re making it extremely easy for users to create new columns in their queries based on values from a different column. A typical use case for this includes creating a set of “buckets” or “categories” based on ranges from a continuous value column. For example, categorizing a person’s BMI into “Underweight,” “Normal,” “Overweight” or “Obese” based on well-known BMI value ranges, or defining categories such as “This Week,” “Last Week,” etc. based on a Date column.
Previously, this could be achieved by creating Custom Columns and capturing the conditional logic with a set of “if-then-else” statements. These expressions can very soon become extremely complex to author and maintain as new conditions are added.
Starting with this update, users can now define a set of rules and output values for the new column based on values in other columns within their tables. This can be achieved via the new Conditional Columns dialog, available in the Query Editor under the “Add Column” tab on the ribbon.
Column type indicator in Query Editor preview column headers
With this update, we added column type indicators in column headers within the Query Editor preview. These new column type indicators allow users to quickly understand the types for each of the columns in their table, as well as change them to another type by clicking on these indicators or by using the previously available ribbon options (Data Type drop-down menu at the Home tab on the ribbon or Detect Type command under the Transform tab).
Reorder Queries and Query Groups inside Query Editor via drag and drop gestures
Users can now easily reorder queries and query groups within the Queries pane inside Query Editor by selecting one or multiple objects and dragging and dropping them into the desired destination. This can be used to reorder items within a given group (or top level) and also to move objects into a query group.
Adding drag and drop gestures to the Queries pane inside Query Editor greatly improves the user experience for reorganizing queries and query groups, which was previously only possible via Context Menu options.
Query Management menu in Query Editor
With this update, we’re exposing a new “Manage” drop-down menu for a query within the Query Editor Home tab on the ribbon. This menu exposes common management operations for a query, such as Delete, Duplicate and Reference.
How do I get started?
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. Updates outlined in this blog are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. 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.
- Learn more about what’s new in Excel 2016.
- Join our Excel community on Facebook and Twitter.
- Send us your ideas for other improvements on our Excel UserVoice page.
—The Excel team
