Windows 10: October 2016 updates for Get & Transform in Excel 2016 & Power Query

Brink · Oct 26, 2016

Excel 2016 includes a powerful new 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 three 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:

Query Parameters support.

Improved Web connector—web page previews.

Query Editor improvements—option to Merge/Append as new query.

Query Parameters support

With this update, users can now create and manage parameters for their queries within the Excel workbook. The new “Manage Parameters” dialog is available on the ribbon under the Home tab within the Query Editor.

The new dialog allows the users to create new parameters, give them a meaningful Name and Description, specify the information about the expected parameter type and values, default value and current value.

Once one or more parameters are available in the current workbook, users can reference those parameters in their queries via Query Editor. Referencing parameters is supported via the Data Source dialogs, Filter Rows, Keep Rows (top/bottom, etc.), Remove Rows (top/bottom, etc.), Replace Values, Add Conditional Columns dialog and more.

In addition, parameters can be loaded to the grid or to the Data Model just like any other query, allowing references from Excel formulas or DAX measures.

The in-depth tutorial on query parameters will be coming on the Excel blogs portal soon. Stay tuned.

Improved Web connector—web page previews

One of the most unique Get & Transform connectors is the Web connector. With the Web connector, users can easily import data from websites that has been formatted as an HTML table or even pull data from Web APIs.

When using the Web connector for “scrapping” data from HTML pages, a very common challenge is that the Navigator view, which is based on a list of tables, is not very helpful in identifying the desired tables. This is particularly hard when dealing with web pages that contain lots of tables, and in many cases, with not very representative table names.

With this update, we’re introducing a new mode in the Navigator dialog that allows users to preview tables on the web pages “in context” and select the desired tables by just clicking on them within the Web View preview. This results in a much more intuitive and seamless user experience for selecting tables from a web page.

To access this mode, click the Web View button at the top of the Navigator dialog. Users can also switch back to the classic data-centric view by selecting the Table View option.

Query Editor improvement—option to Merge/Append as new query

Within the Query Editor, users can easily merge (join) or append (union) multiple tables, allowing them to mash up data from multiple sources into a single table. The Merge/Append operations are on the ribbon under the Home tab inside Query Editor.

In previous versions of the Query Editor, Merge/Append operations were always applied as new steps within the current query. Starting with this update, users can decide whether to apply these operations as a new step in the current query (old behavior) or whether the output of the Merge/Append operation should be created as a new query (new behavior).

How do I get started?

Excel 2016 provides a powerful set of capabilities for fast, easy data gathering and shaping, which is available under the Get & Transform section on the Data ribbon. Updates outlined in this blog 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.

Learn more about what’s new in Excel 2016.

Join our Excel community on Facebook and Twitter, and collaborate with others on Get & Transform data at our new Tech Community forum.

Send us your ideas for other improvements on our Excel UserVoice page.

—The Excel team
Click to expand...

Source: October 2016 updates for Get Transform in Excel 2016 and the Power Query add-in - Office Blogs

Tip How to Check for Updates in Office 2016 for Windows

:)

gourdguru · Oct 26, 2016

Windows 10 anniversary update causing all kinds of problems

I seem to be having a problem installing an update.

Windows Malicious Software Removal Tool for Windows 8, 8.1, 10 and Windows Server 2012, 2012 R2 x64 Edition - October 2016 (KB890830)

It says it failed to install but when I go into the updates & check for updates it says everything is up to date & there's no way for me to install it.

Any suggestions.

memainakdutta · Oct 26, 2016

security & protection

which is the best & powerful antivirus protection for windows 10 pro in 2016??

Joanne · Oct 26, 2016

Hey Brink, I am using the "standalone" version of Office 2016 Professional Plus and I do not have this feature in my Excel. Am I not supposed to get it? Or should I activate some Add-on in the Options?

Thanks! *Smile

Brink · Oct 26, 2016

Hello Joanne, *Smile

These updates are available as part of an Office 365 subscription.

Joanne · Oct 26, 2016

Ah, okay, I see, thank you Brink *Smile

It's just that I've read that people with Office 2013 and even 2010 can get this, and I thought it would be available for us, as well.

Thanks anyway *Smile

Brink · Oct 26, 2016

Maybe one day hopefully soon.

Scottyboy99 · Apr 4, 2018

I checked for updates on my 365 and it says already upto date. Nothing new coming through, is the latest public build still 16.0.7369.2038

Windows 10: October 2016 updates for Get & Transform in Excel 2016 & Power Query

October 2016 updates for Get & Transform in Excel 2016 & Power Query

October 2016 updates for Get & Transform in Excel 2016 & Power Query

October 2016 updates for Get & Transform in Excel 2016 & Power Query

October 2016 updates for Get & Transform in Excel 2016 & Power Query

October 2016 updates for Get & Transform in Excel 2016 & Power Query - Similar Threads - October 2016 updates

Excel 2016 freezes

Excel 2016 freezes

Excel 2016

Excel 2016

Excel 2016

September 2016 updates: Get & Transform in Excel 2016 and Power Query

May 2017 updates for Get & Transform Excel 2016 & Power Query add-in

April 2017 updates for Get & Transform in Excel 2016 and Power Query

New feature updates for Power Pivot in Excel 2016