Modernized Data Import and Shaping Experience
Published Jul 19 2017 01:40 PM 15.7K Views
Microsoft

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

15 Comments
Copper Contributor

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

Copper Contributor

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

 

Copper Contributor

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

Copper Contributor

I'm attempting to import data from an external excel file based on a url and none of these options seem to be able to do that.  Is there another path to import data from an external web based excel source?

Microsoft

@Scott Moore, try using the new From Web wizard and specify the URL to your Excel file. It worked in my case. Like this:

Screenshot_1.png

Copper Contributor

I just recently updated Office and Excel no longer has the Data Connections window.  I now get the Queries and Connections panel, which is lacking in functionality, I can refresh a data connection from there, but it doesn't show the date and time of the last refresh, this is absolutely vital when working with data models where data is loaded regularly, or when you've been in the model several times during the day to make data changes, you simply need to see the time of the last refresh.  The former Data Connections window showed the connections and the date/timestamp along with a refresh button right there.  Now I need to right click the connection name from the panel and go to Properties to see the timestamp, but there is no refresh button in the properties window.  Please add a date/timestamp to the Queries and Connections panel!

Microsoft

@Karen Price, thanks for your feedback. I would encourage you to vote for your idea at Excel User Voice here:

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application?category_id=143418

 

It helps us to prioritize our resources going forward.

 

Guy

- Excel Team

Copper Contributor

Hi Microsoft,

Since the last Excel Update, SQL queries will not refresh on a protected workbook. This causes multiple issues as the basis of a lot of excel modelling is pulling information from multiple sources. Basically this has corrupted a number of reports and models I have in excel. It is standard practice to allow users to ‘refresh’ model information but keep the file protected from tampering. As far as I can see, this is a bug as Workbook protection is only on structure and refreshing a SQL extract is not altering the structure in any way. Please can this be looked into as this has caused multiple issue in our organizations reports and models. If you have a work around can you also please let me know.

Deleted
Not applicable

Hi all,


I'm struggling to understand why in my office 2016 I don't have access to Export Connection File in order to export a query.

 

based on the tutorial: 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. However, this command is disabled.

 

If I also tried to go to Query properties and Definition this is also not available..

 

 

Can you please help me with this issue?

Thank you in advance

Microsoft

Hi  J. Augusto Oliveira,

 

What's your Excel version please?

 

Guy.

- Excel Team

Copper Contributor

This process to import data from the web broke between April 2018 and July 2018. I have done this successfully many times and now I get an error. Recently, we upgraded to Excel subscription 365 (using it on my desktop), and it still doesn't work. 

 

I've copied details and reported it to Microsoft, but I'm sure they are busy doing other things... no fix yet

 

 

Capture.JPG

Microsoft

@Rajshree Chauhan,

 

Can you please share your Excel version and the URL you are trying to import from?

 

Guy

- Excel Team

Copper Contributor

I am in need of some help.  I have been trying to figure this out but can not.  I keep my payroll information in a excel file.  I have one sheet per month with everyones information on it.  I need to be able to pull information about one employee  off of 12 sheets to one sheet per employee.  

Version history
Last update:
‎Jul 19 2017 01:44 PM
Updated by: