Skip to main content

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

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

You may also like these articles

Image for: A woman sitting on a couch using her PC laptop.

Introducing Money in Excel, an easier way to manage your finances

Today, with the launch of Money in Excel, we are embarking on a journey to simplify your day-to-day money decisions to help you stay on track of your longer-term financial goals.

Image for: A man working from home.

Get Yammer in Teams and new features in Microsoft Edge—here’s what’s new to Microsoft 365 in April

In this world of remote everything, our customers are discovering so many new ways to work and learn online. We’re committed to shipping updates and improvements to our tools that help you stay connected, productive, and secure through the current crisis and beyond.

Image for: Image of a father and daughter on a couch.

Introducing the new Microsoft 365 Personal and Family subscriptions

The subscription service for your life to make the most of your time, connect, and protect the ones you love.