January 2018 updates for Get & Transform in Excel 2016 and the Power Query add-in
Published Jan 18 2018 07:49 AM 10.3K Views
Microsoft

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. 1.png

 

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.

2.png

 

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.

3.png

 

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.

4.png

 

Guy Hunkin

— Excel Team

 

 

11 Comments
Brass Contributor

The bucketing feature is a great enhancement. A useful improvement to this feature would be to also automatically add a rangeIndex column based on the same principle. This column could then be used to sort the Range column in the data model. Otherwise, ranges may be displayed in the wrong order in visuals or pivot tables.

Microsoft

@Bertrand D arbonneau, I encourage you to vote for your idea at PowerBI User Voice here:

https://ideas.powerbi.com/forums/265200-power-bi

 

It helps us to prioritize our resources moving forward.

 

Guy

- Excel Team

Copper Contributor

I have MS office 2016 locally installed on PC. How do I get the updates? 

 

Thanks 

Microsoft

@Majed Munazzit,

 

Please refer to the following article for more details:

https://support.office.com/en-us/article/update-office-with-microsoft-update-f59d3f9d-bd5d-4d3b-a08e...

 

Guy

- Excel Team

Copper Contributor

Hi Guy, 

I would like to seek your help with an issue regarding Power Query. At the Add column part we are not able to find the conditional Column, just the Add Custom Column, Add Index Column, Duplicate Column and nothing else. What is the way to add/enable Conditional Column? Office 2016 Professional not Office365. 

 

 

Regards,

Zsolt

Copper Contributor

@Guy Hunkin

It worked. Many thanks

 

Best, 

Majed

Microsoft

@Zsolt Rajna,

 

Can you please share your Excel and Power Query versions? The Excel version go to the File tab on the Excel ribbon > choose Account > Product Information. For the Power Query version go to Query Editor > choose File tab on the ribbon > Options and settings > Query Options > Diagnostics > Version.

 

Guy

- Excel Team

Copper Contributor

Hi @Guy Hunkin

 

I am also experiencing the same issue as @Zsolt Rajna

 

I do not have the option to select "Conditional Column" within the "Add Column" tab

 

Product information is Microsoft Office Professional Plus 2016; Excel 2016; 64 bit

 

Power Query Version: 2.24.4065.1401

 

Thank you very much in advance!

 

@Karlena Bucknor, I guess it's still for Office 365 subscribers only, perhaps for Excel 2019

Copper Contributor

Guys, help me to update the version I have from 2.24.4065.1401 to the newest version. we use office subscribed by the company not standalone . some of my colleagues have the newest version of power query.

@Ala'a Radaideh , this clarification https://social.technet.microsoft.com/Forums/en-US/496325f1-bad0-49fd-a293-be654ccaf67d/power-query-v... regarding the versioning and updating is still actual.

Version history
Last update:
‎Jan 18 2018 09:03 AM
Updated by: