Back
Excel

January 2017 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 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 six 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:

  • New OLE DB connector.
  • Enhanced “Combine Binaries” experience when importing from any folder.
  • Maximize/Restore buttons in the Navigator and Query Dependencies dialogs.
  • Support for percentage data type.
  • Improved “Function Authoring” experience.
  • Improved performance for OData connector.

New OLE DB connector

In this update, we enabled connectivity to OLE DB drivers via the new OLE DB connector. In addition to the wide range of out-of-the-box sources supported, OLE DB greatly increases the number of sources that users can now import from by using Get & Transform capabilities in Excel.

The new OLE DB connector can be found under Data > New Query > From Other Sources > From OLE DB.

The connector dialog allows users to specify a Connection String and, optionally, an SQL statement to execute. If no SQL statement was specified, users will be taken into the Navigator dialog, where they can browse and select one or multiple tables available via the selected OLE DB driver.

Get and Transform January updates 1

Enhanced “Combine Binaries” experience when importing from any folder

One of the most popular scenarios in Excel consists of leveraging one of the folder-like connectors (such as Folder, SharePoint folder, etc.) to combine multiple files with the same schema into a single logical table.

Before this release, users could combine Text or CSV files only. The combine would not work for any other supported file formats (such as Excel Workbooks, JSON files, etc.), and it would not account for transformations required on each file before combining them into a single table (such as removing the first row with header values).

With this release, we enhanced the “Combine Binaries” experience when importing from any folder so that:

  • Excel analyzes the input files from the Folder query and detects the right file format to use (i.e., Text or Excel Workbook).
  • Users can select a specific object from the list (such as a spreadsheet name) to use for data combine.
  • Excel automatically creates the following entities:
    • An example query that performs all required transformation steps in a single file.
    • A function query that parameterizes the file input to the exemplar query created in the previous step.
    • Excel then applies the created function query on each file from the original Folder query and expands the resulting data extraction as top-level columns.

With this new approach, users can easily combine all binaries within a folder if they have a homogeneous file type and column structure. Users can also easily apply additional transformations by modifying the “exemplar query” without having to worry about any additional function invocation steps, as they’re automatically generated for them.

Get and Transform January updates 2

Maximize/Restore buttons in the Navigator and Query Dependencies dialogs

The Navigator and Query Dependencies dialog (activated from Query Editor) support window resizing by dragging the bottom-right edges of the dialog. In this release, we made it possible to maximize/restore these dialogs by exposing Maximize and Restore icons in the top-right corner of the dialogs.

Get and Transform January updates 3

Support for percentage data type

With this update, we added support for percentage data types, so they can easily be used in arithmetical operations for Get & Transform scenarios. An input value such as “5%” will be automatically recognized as a percentage value and converted to a two-digit precision decimal number (i.e., 0.05), which can then be used in arithmetical operations within a spreadsheet, the Query Editor or the Data Model.

Besides automatic type recognition from non-structured sources (such as Text, CSV or HTML), users can also convert any value to percentage using the Change Type options in the Query Editor. You can do this on the Query Editor Home tab, on the Transform tab, by clicking Data Type > Percentage, or right-clicking a column and then selecting Change Type > Percentage.

Get and Transform January updates 4

Improved “Function Authoring” experience

We also made it easier to update function definitions without the need to maintain the underlying M code.

Here’s how it works: Create a function based upon another query using the “Create Function” command. You do this by right-clicking the Queries pane inside Query Editor. When you do that, a link will be created between the original query and the newly generated function. This way, when the user modifies the original query steps, the linked function will be automatically updated as well.

When using Query Parameters, creating a function out of a query will allow users to use Function Inputs to replace parameter values in the generalized function query.

Improved performance for OData connector

With this update, we added support for pushing Expand Record operations to be performed in the underlying OData service. This will result in improved performance when expanding records from an OData feed.

Learn more

—The Excel team

Top

Join the conversation

16 comments

  1. Hej Excel Team,

    thanks for another great PQ update! Any news for intellisense-like support in PowerQuery formula bar and custom column?

    Sz.

  2. All lovely stuff but… Please make the “UPDATE” icon in the ribbon take the user to the download page for Power Query rather than this page. Or… include an obvious link to the download page ON this page…

    • Thanks for the feedback, Matt. We’ll make sure that the PQ add-in download link is more prominent in future update announcements in this blog. We’ll definitely continue taking users to this page as it has a good summary of new features in the release.

      Thanks,
      M.

  3. The text says “The new OLE DB connector can be found under Data > New Query > From Other Sources > From OLE DB”. This is probably for Excel 2016. The text furthermore says “If you have Excel 2010 or Excel 2013, you can also take advantage of these updates”. But where can I find “From OLE DB” in Power Query using Excel 2013?

    Best regards,
    Helge

    • Hi Helge,

      New connectors are available only for Office 365 subscribers.

      Guy
      – Excel Team

  4. How can I get this update for 2016 WITHOUT Office 365 subscription?

    It seems ridiculous that this is available for 2010, 2013 BUT NOT 2016?

    • Hi Jeremy,

      We do release all the Get & Transform updates to Office 2016 perpetual customers with the exception of the new connectors and Excel integration related features, which are reserved for Office 365 subscribers only.

      This specific update (excluding the new OLEDB connector) is planned to be released on March 14 as a regular KB that you’ll be able to download and install from the Office Support site. Here is an example of such an update for one of the previous releases:
      https://support.microsoft.com/en-za/help/3115185/update-adds-get-transform-features-in-excel-2016

      Guy
      – Excel Team

    • Hi Jeremy,

      Also worth mentioning that you can install the updates for Office 2016 perpetual version via Windows Update. There is no need to explicitly download and install the specific KB from the Office Support site.

      Guy
      – Excel Team

      • Cheers

        It DID allow me to update via File ->Account ->Office updates ->Update now

        Probably because I am ALSO an O365 subscriber.

        Where can I report issues with this version?

        • Hi Jeremy,

          If you have the Update Now button it means that you are running a click-to-run version of Office (it can be either perpetual or Office 365 subscription), so you are all set. The update will be downloaded and installed for you automatically.

          Regarding you question:
          a. If you are an Office Insider then you can provide feedback via File > Feedback > Send a Smile or Send a Frown.
          b. If you are not an Office Insider then you can report an issue via the Excel forum on Microsoft Answers Community (https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel).

          Guy
          – Excel Team

  5. Hi,
    I have the previous version installed. How do I stop the upgrade pop up notification that links to this site?

    Thanks

    Regards

    Claus

    • Hi Claus,

      If you’re using the PQ add-in for Excel 2010/2013, you can disable update notifications from the Options dialog in the Power Query ribbon tab. Within this dialog, there is a section for Updates.

      Thanks,
      M.

      • Hi,

        Thanks for the update. Is there a GPO or registry key that disables it? Then I can deploy it from a GPO.

        Thanks

        Regards

        Claus