Home
Microsoft

August 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

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.

1.png

 

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

11 Comments
Frequent Contributor

It would be nice if this explained the version required to get this given the fast ring/slow ring/current channel/deferred channel stuff (or whatever the new names are).

Occasional Visitor

Guys  please add "Fuzzy Logic" to Power Query, MS reasearch had a plugin for Excel, but that doesn't work anymore. If you can't do it at least give us a hint using the package Stringdist for R Stats (as a R script). This is a crucial feature for many users. I hope to get a feedback on this guys! Thanks again for all your efforts!

Great to see continuous improvements.  A Fiscal Year and Fiscal Month transformation would be the icing on the cake and make a lot of people I know happy :)

 

 

Regular Visitor
Guy, Could you, please, share the Excel version containing these updates? Thanks
Microsoft

@Ed Hansberry & @Cristian Angyal, these updates are avaiable starting from Office version 1707 (Build 8326.2076). I'll make sure to explicitly mention it in the future announcments.

 

Guy

- Excel Team

Microsoft

@Patricio Lobos & @Wyn Hopkins, the best way to unfluence will be to vote for your ideas at Power BI User Voice here. It helps us to prioritize our investments going forward.

 

Guy

- Excel Team

Occasional Visitor

Thanks @Guy Hunkin, Fuzzy Logic posted  since june 2015, but no luck, so far only 139 votes!

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8206656-fuzzy-comparison-function...

Occasional Visitor

HEllo

I do not have Office 365 but Excel 2016. I cannot see the link to Business Object which was present in Excel 2013. Any hint on how to address this?

Kind regards

Giorgio

@Guy Hunkin, perhaps in addition to Excel build where G&T changes are introduced it's worth to mention as well the PQ add-in version (current one 2.48.4792.941), as i understand they are more or less in sync. Quite many people are still on 2013 and even on 2010.

Microsoft

@giorgio lupo, we do not support SAP Business Objects connector in Excel anymore.