Today, we released yet another set of updates for Get & Transform — a powerful set of Excel 2016 features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities.
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 (note that the add-in may take several hours to appear at the download site).
With this update, we added support for generating "Conditional Columns" transformations as a part of the "Add Column From Examples" feature, which can be accessed via "Add Column" tab on the Query Editor ribbon. By supporting "Conditional Columns" transformations the following scenarios are now unlocked:
Basic Conditional Column
It is now possible to define a mapping between values in an input column and the desired output by providing a set of examples.
Conditional Column Ranges
With this update, the users can define a new column with non-uniform ranges based on an input column. This new capability is supported for columns where the output values don’t encode the range boundaries.
Bucketing
Starting with this month's release, "Add Column From Examples" also supports bucketing. Users can specify the upper/lower boundaries of a range for a certain row and Excel will automatically extrapolate to all other rows by using uniform ranges.
Null Fallback
A very common scenario for “Conditional Column” is using a value from a given column, or the value from a fallback column when the first column value is missing. "Add Column From Examples" feature now supports this kind of transformations for you.
Guy Hunkin
— Excel Team