Excel 2016 introduced a powerful set of tools called Get & Transform. These tools, based on Power Query technology, enable you to easily connect, combine, and shape data coming from a variety of sources.
Today, we are pleased to announce the next major update to data import and shaping functionality in Excel. With this update, we enhanced Excel’s data import functionality and rearranged the ribbon so that it's easier than ever to take full advantage of Get & Transform capabilities.
NOTE: These updates are available as part of an Office 365 subscription starting from Version 1704 (Build 8067.2115) and later. If you are an Office 365 subscriber, find out how to get these latest updates.
Modernized Get & Transform Experience
With the modernized Get & Transform experience, you benefit from the well-integrated and modern set of data import and shaping capabilities in Excel. You can easily connect and get data from a variety of popular data sources such as files, databases, Azure and Online services, and more. You can combine data coming from different sources, and you can shape it in powerful ways by sorting, changing data types, splitting columns, aggregating the data, and so on.
The constantly growing offering of new connectors and easy-to-perform transformations are what make Get & Transform an invaluable and time-saving tool in your day-to-day work.
Import data from CSV files, Web and other sources
With the new Get & Transform experience you can easily connect and import data from a variety of data sources. You can start by importing data from Text/CSV file, from Web or from Excel table/named range – these top three most popular connectors are available for you under the Get & Transform Data section on the Data tab of the ribbon.
Or you can choose to import data from a variety of other sources available under the Data > Get Data drop-down menu. The supported data sources are organized for you in categories, including files like Excel workbooks, XML files or the entire folder, databases such as SQL Server or Oracle, Azure services such as HDInsight or Blob Storage, Online services like SharePoint Online List or Salesforce, and a variety of other sources like OData or ODBC.
Clean and shape your data
Get & Transform lets you clean and shape your data as an essential step for robust data analysis. You can remove columns, filter rows, change column data types, replace values and more. This is done through the dedicated Query Editor window that facilitates and displays all your data transformations.
Switch to the Data tab on the ribbon, click Get Data, connect to the desired data source (such as file or database) using one of the available connectors, and preview your data in the Navigator window.
Next, select the tables that you would like to import and click Edit to launch the Query Editor window.
Use the wide collection of commands available from the ribbon inside the Query Editor window. You can apply powerful transformations by sorting and filtering data, converting data types, splitting or merging columns, aggregating your data and more.
All the transformation steps that you apply on your data are recorded and are saved as a part of your query. The recorded transformations can be observed and managed via the APPLIED STEPS section inside the Query Editor window and will be re-applied to your data every time your query is being refreshed.
Manage existing workbook queries and connections
With this update, we introduced a consolidated management experience for workbook queries and connections. In the new unified management experience for queries and connections, users can now access them all through the dedicated Queries & Connection side pane. To open the side pane, click the Data > Queries & Connections button.
Clicking on the Queries tab at the top of the side pane allows you to browse and manage the available queries in your workbook. Right click on a query to access the query’s context menu that allows you to perform additional actions such as manage query properties, refresh query data from the underlying source, edit query transformations with Query Editor and more.
In a similar manner, clicking on the Connections tab at the top of the side pane allows you to browse and manage the available legacy connections in your workbook that are associated with one of the legacy wizards such as legacy From Text, From Analysis Services or From Microsoft Query. Right click on a connection to access the connection’s context menu that allows you to refresh connection data from source, delete connection or manage connection properties.
Get data from recently used sources or Office Database Connection (ODC) files
Excel users often establish a set of frequently used sources they import data from. With this update, you can easily create a query from one of your recently used data sources by clicking on the Data > Recent Sources command. You can then browse through your recently connected sources, select one, and then click Connect to continue with the import flow.
Another popular scenario among Excel users is sharing your workbook queries with other people in your organization. With this update, it is now possible to import and export queries via the commonly used Office Database Connection (ODC) mechanism.
To export a query definition into an ODC file, right click on a query in the Queries & Connections side pane and choose Export Connection File command. The created ODC file will contain the definition of your query including the connectivity instructions to the data source and the transformation steps being applied on your data.
Once you receive an ODC file, you will be able to consume it and import the query definition into your workbook. To create query and import data using the existing ODC file, use the Existing Connections dialog opened by clicking on the Data > Existing Connections button.
Refer to this support article for the full scope of changes being introduced.
We hope that you like the new experience and look forward to your feedback!
Guy Hunkin
—The Excel team