Home
Microsoft

Modernized Data Import and Shaping Experience

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.

1.png

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.

 2.png

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.

3.png

 

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.

4.png

Next, select the tables that you would like to import and click Edit to launch the Query Editor window.

5.png

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.

 6.png

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.
7.png

 

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.

8.png

 

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

5 Comments
Occasional Visitor

Hi Team,

 

I would like to know that if there is a solution to increase the number of criterias (some how like half million) in the power query. 

 

Please let me know the solution or any alternate solution.

 

Regards

srikanth

Hi Sri,

 

Just for my education could you please explain in bit more details what do you mean under criteria in PQ and how do you manage to generate half million of them?

 

Thanks

Occasional Visitor

I am receiving the following error when trying to use this feature.  I have searched and searched for solutions, but am not having any luck. Can you assist me in figuring out how to turn off the IE compatibility? I do not use IE and it is disabled on the computer.

 

 excel error.png

Sue, in general that's not an error - you selected the page with no one table on it, what you'd like to pick up from this page? If take the link like http://www.paintedparadiseresort.com/Red_Bluff_Web_2013.html and select Table view  for the table that works correctly

TableView.JPG

 

Occasional Visitor

Thank you for the response. I am still receiving the same message. While I can get the table to import, I am unable to edit the query and apply full HTML formatting. This is an exercise for a class and the help desk/instructor have not come up with any solutions for me. Our instructional video says to load the data from the web view. This is for a college class, so any assistance in why this is happening would be appreciatedexcel2.png