Back
Excel

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

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

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.

october-2016-updates-for-get-transform-1

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.

october-2016-updates-for-get-transform-2

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.

october-2016-updates-for-get-transform-3

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.

october-2016-updates-for-get-transform-4

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.

october-2016-updates-for-get-transform-5

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.

october-2016-updates-for-get-transform-6

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

october-2016-updates-for-get-transform-7

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.

—The Excel team

Top

Join the conversation

20 comments
  1. Still waiting for the ability to loop a query for data that requires cursored pulls from an API (i.e., Twitter). I currently do this in PowerShell to produce a JSON file but it would be better if I could do it directly in Excel.

    Either that, or add a feature to let Power Query call an external program or script to pull the data and import the resulting output.

  2. “The in-depth tutorial on query parameters will be coming on the Excel blogs portal soon.”

    Where is that? I’d love an RSS feed to the excel blog. Googling didn’t help.

  3. All wonderful new features. Thanks! Looking forward to trying them out.
    Now if we can figure out how to pass params to a Salesforce.com Report (which is possible with URL params) that would be super-sweet.

    • Hi Dory,

      Try switching to an Advanced mode when you open the Import From Web connector. Once you switch to the Advanced mode, you will see an option to construct your URL from multiple parts. Each of such parts can be based on a value coming from a predefined parameter.

      Hope it helps.

      Guy
      -Excel Team

  4. The web page preview is amazing! I started using it with my HTML tables website and its working perfect – making my life easy 🙂 The only thing is that for me, its working slow, but maybe you guys will be able to improve that feature in the future
    http://customerservicecontactnumber.uk/dvla/

    • Hi Daniel,

      What is the URL that works slow for you? Is it the one that appears at your comment?

      Guy
      – Excel Team

  5. Well the whole parameters thing doesn’t seem to work very well with Excel 2010.
    First, if you want to populate a list of Suggested Values using a query you can only select a query from the list if you start Excel, open a workbooks, go directly into the Power Query editor and add a new parameter. If you try after doing anything else, the drop-down to select a query is not functional. When it is functional, it only shows a partial list of queries available in the workbook.
    Once you have managed to create a query driven parameter, when you try to use it or a filtering operation, you run straight in to the formula firewall problem.
    If you create a query with a hard-coded list of suggested values, you don’t run in to the formula firewall problem, but when the query is refreshed, you are not prompted to select a value from the list. So what’s the point of having a Parameter if you have to manually change the parameter value in the Power Query editor in order to change the value passed to another query (or am I missing something here)?
    Finally, the most useful and desired source of a parameter value is not even available; a cell reference in the workbook.
    Maybe the promised “in-depth tutorial” will solve the mystery of all of these shortcomings, but right now, the Parameters functionality doesn’t seem very useful.

    • Hi Douglas,

      There are several issues that you mentioned here. Let me try to address them one by one.

      Regarding populating a list of Suggested Values: if you are trying to select a query as a source for Suggested Values, then you will only be able to select from a list of queries that actually return a list. This is pretty much by design. You won’t be able to select a function query, for example.

      Regarding running into the Formula Firewall problem: well, if your query loads data from the web for example, and the list of parameters is populated from a local query within your workbook – then applying a parameter on your query actually means that you are trying to combine data with different trust levels, in which case the Privacy Levels dialog will show up. This is by design, as well.

      Regarding prompting for a query value on refresh: with this update we made a first step of supporting query parameters within Excel. The behaviour exists today allows you to manually change the parameters values from within the Query Editor. This is definitely useful in authoring scenarios, but less useful in consumption scenarios.
      We will be looking to improve the query parameters in Excel going forward. In a meantime, I encourage you to promote your ideas and scenarios at Excel User Voice portal here:
      https://excel.uservoice.com/
      This will help us to prioritise our efforts going forward.

      Regarding having your parameter values coming from a grid: this is definitely something that we want to consider. In a meantime, the best way to influence will be to publish your idea at the Excel User Voice portal (https://excel.uservoice.com/. As I mentioned, this will help us to prioritise our investments.

      Guy
      – Excel Team

  6. Hoping that you soon can connect and import data from Azure Data Lake Store. Is this on the roadmap for Get & Transform?

    • Yes, we are planning to add the Azure Data Lake Store connector. However, there are no specific timelines yet.

      Guy
      – Excel Team

  7. I’ve got Power Query installed on Office 2013 (not 365) and its saying an update is available but its navigating me here and there seems to be no update option – am I missing something?

    • (See above 3rd paragraph for the link)
      “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.”

  8. I like the new query parameter feature, How can you reference a cell value from a worksheet and use it as a query parameter?

    • Hi Jennifer,

      Supporting worksheet as a source for Query Parameters is definitely something that we want to provide going forward. To promote your idea, I suggest you vote for it at Excel User Voice portal here:
      https://excel.uservoice.com

      This helps us to prioritise our investments going forward.

      As a workaround, I suggest you create a parameter that is based on a list of values.

      Guy
      – Excel Team

  9. Hello!
    Why in the line POWER query query formulas is not possible to refer to the EXCEL cell? Make the request form – 1) address of the source, 2) a formula. So that you can refer to a cell in which the request is formed. Following the example of the INDIRECT () or CONCATENATE (). That is EXCEL.

    • Hi there!

      You can actually refer from Query Editor to any named range or table in Excel. Start Query Editor via Data > New Query > From Other Sources > Blank Query, make sure the formula bar is displayed (View tab > Formula Bar) and type the following command in the formula bar:

      = Excel.CurrentWorkbook()

      This will display the list of all tables and named ranges in the current workbook, and you will be able to expand the tables until you find the data you need.

      Guy
      – Excel Team

Comments are closed.