Today, we released yet another set of transformation 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.
These updates include the following enhancements to the recently released "Add Column From Examples" feature, which can be accessed via "Add Column" tab on the Query Editor ribbon:
Suggestions
As users go into “Add Column From Examples” we will show suggestions for output values based on the contents of other columns and partial contents typed by the user. These suggestions also help disambiguate between multiple transformations where the output value might be the same for a given row.
Support for Additional Transformations
We added support for many additional transformations, including Number, Date, Time and Timezone operations. Here is the full list of transformations that we are currently supporting:
- Reference — Reference to a specific column including trim, clean, and case transformations.
- Text transformations
- Combine (supports combination of literal strings and entire column values)
- Replace
- Length
- Extract
- First Characters
- Last Characters
- Range
- Text before Delimiter
- Text after Delimiter
- Text between Delimiters
- Length
- Date transformations
- Day
- Day of Week
- Day of Week Name
- Day of Year
- Month
- Month Name
- Quarter of Year
- Week of Month
- Week of Year
- Year
- Age
- Start of Year
- End of Year
- Start of Month
- End of Month
- Start of Quarter
- Days in Month
- End of Quarter
- Start of Week
- End of Week
- Day of Month
- Start of Day
- End of Day
-
Time transformations
- Hour
- Minute
- Second
- Date/Time/Timezone transformations
- To Local Time
- Number transformations
- Absolute Value
- Arccosine
- Arcsine
- Arctangent
- Convert to Number
- Cosine
- Cube
- Divide
- Exponent
- Factorial
- Integer Divide
- Is Even
- Is Odd
- Ln
- Base-10 Logarithm
- Modulo
- Multiply
- Round Down
- Round Up
- Sign
- Sin
- Square Root
- Square
- Subtract
- Sum
- Tangent
Guy Hunkin
— Excel Team