Please note javascript is required for full website functionality.


January 2018 Updates for Get & Transform / Power Query

19 January 2018

Today sees the release of the next set of updates for Get & Transform, the powerful Excel 2016 feature based on the Power Query technology.

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 latest Power Query for Excel add-in too (although Microsoft states that you should note that the add-in may take several hours to appear on the download site).

January’s update adds support for generating 'Conditional Columns' transformations as a part of one of our favourite tools, the ‘Add Column From Examples’ feature, which may be accessed via ‘Add Column’ tab on the ‘Query Editor’ Ribbon. By supporting these new ‘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: users can now define a new column with non-uniform ranges based on an input column
  • Bucketing: users may 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 is using a value from a given column, or the value from a fallback column when the first column value is missing.

Please remember we have training in Power Query which you can find out more about here. We’ll be detailing these updates in our February newsletter, which is looking like it will be our largest newsletter ever. If you are not already a subscriber, why not sign up at the bottom of any SumProduct web page?