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

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

Excel 2016 includes a powerful set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon.

Today, we are pleased to announce two new data transformation and connectivity features that have been requested by many customers.

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 new or improved data connectivity and transformation features:

  • New transformation—horizontal list expansion.
  • Enhanced SQL Server connector—support for SQL Failover option.

New transformation—horizontal list expansion

With this update, we made it easier to extract data values from a column containing nested lists. Before this update, customers could expand nested lists within a column in a table, resulting in one new table row for each item within the nested list. This capability is accessible via the column headers in a List column, or by using the Expand ribbon entry point.

We also added a new Extract Values transformation command that allows users to extract values from a list into a new Text column, with a delimiter in between these values. This new transformation can be accessed from the column header when a column with nested Lists is selected.

Upon selecting this transformation, users are prompted to provide a delimiter to use in the new column. They can pick from a list of predefined delimiters or specifying a custom one, which may also include special characters.

This transformation turns the column with nested lists into a Text column as showed below:

Enhanced SQL Server connector—support for SQL Failover option

We improved the SQL Server connector and added a new option to enable SQL Server Failover support. This new option can be found under the Advanced Options section in the SQL Server connector dialog. See “Always On Failover Cluster Instances (SQL Server)” for details about this option.

Learn more

—The Excel team

  1. Just received a pop-up stating “View and restore changes in workbooks that are shared.”

    Would like to know more about this, but don’t see it here in March 2017 new features.

    Can anybody shed light on this?

  2. Hello!
    Thanks for Get & Transform, I really enjoy using it.
    I have a question about it and I don’t know where I should ask such things. So, please answer my question or tell me where to write it to get an answer.
    In our company we have to use the information about sales in different reports, so I have some different excel files for different users. Each file contains a pivot table that comes out from the data model. And each file has some queries. But all of the files have a query that loads information about sales (I save the data about sales from our own software as txt files every month in a special folder) using the same steps.
    But our IT department sometimes changes the form of report about sales (for example, they may add some new columns, rename columns etc.). And if it happens I need to delete all of the historical data and load it again (in new farmat) and to change the queries that use that data.
    And the problem is that I have to keep in mind all the files where I use that queries to change it. Bur if they are identical, do I have any opportunity to change the query once in any place, so the others would change automatically?
    If it possible, please try to answer in simple words, because I am not native english speakers. I hope I describe my problem quite understandable and sorry for possible mistakes.

  3. Where is the link to download the Get & Transform add-in. I just cannot find it.

  4. Hi – since the latest power query update, every time I flick back and forth between either external programs or different workbooks in the same excel session (via Alt Tab or Cntrl tab), Power Query now places my “cursor” into the workbook query window. This means I then need to use the mouse to “click” into the workbook..

    This might not sound like much of an issue – but for someone who tries to use keyboard shortcuts as much as possible – it’s frustrating.
    Does anyone know any workarounds for this?

    Of note – if you have the workbook query window closed – it does not occur – but for much of what I do, I prefer to have it remain open.


    • I knew this would happen – after I finally spent the time to type this question – I can no longer seem to emulate the issue!
      Hmmm. Tricky.
      I’ll monitor and report back if I figure out the pattern of events that is causing it.

  5. Are there any plans to incorporate features that are released in power bi desktop.

    1) Recently announced “Add column by Example”
    2) Split column to rows using delimiters or no. of characters
    3) Any eta for intellisense ?


Comments are closed.