Back
Excel

July 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 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 12 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 SAP HANA connector.
  • New SharePoint Folder connector.
  • New Online Services connectors category.
  • Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2.
  • Improved Text/CSV connector, now exposing editable settings in the preview dialog.
  • Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy.
  • Data Source Settings enhancements, including “Change Source” capability.
  • Advanced Filter Rows dialog mode within the Query Editor.
  • Inline Input controls for Function invocation within the Query Editor.
  • Support for reordering Query Steps within the Query Editor by using drag and drop gestures.
  • Date picker support for input Date values in Filter Rows and Conditional Columns dialogs.
  • New context menu entry to create new queries from the Queries pane within the Query Editor.

New SAP HANA connector

In this update, we added a new connector to allow users to import data from SAP HANA databases. The new connector can be found under Data > New Query > Database category, or from the Query Editor window.

July 2016 updates for Get Transform in Excel 2016 1

New SharePoint Folder connector

Available under Data > New Query > File category, the new SharePoint Folder connector enables users to import data from multiple files within a SharePoint folder, similar to how the existing Folder connector enables users to import and combine multiple files from a filesystem folder.

July 2016 updates for Get Transform in Excel 2016 2

New Online Services connectors category

With this update, we added a new category that includes all available connectors for Online Services in Excel. The new category is available under Data > New Query > Online Services or from the Query Editor window.

July 2016 updates for Get Transform in Excel 2016 3

Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2

In this release, we improved the IBM DB2 connector to provide users the choice to leverage the Microsoft driver for IBM DB2, which is also automatically included in the Excel/Power Query installation.

Within the IBM DB2 connector dialog, users can now select which driver to use under Advanced options.

July 2016 updates for Get Transform in Excel 2016 4

Improved Text/CSV connector, now exposing editable settings in the preview dialog

In this update, we improved the Text and CSV connectors so users can configure basic import settings from within the preview dialog. These import settings include:

  • File Origin.
  • Delimiter.
  • Detect Data Type (strategies include: Base on top 200 rows, Base on entire dataset or No data type detection).

Based on user choices, the preview in this dialog automatically updates. Users can then decide whether to directly load the data or edit first to apply additional data transformations.

July 2016 updates for Get Transform in Excel 2016 5

Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy

In this update, we enhanced the database connectors to expose an option that allows users to include Schema information as part of the Navigation hierarchy. This option is available under the Advanced option in all database connector dialogs, such as the SQL Server Database dialog below:

July 2016 updates for Get Transform in Excel 2016 6

Data Source Settings enhancements, including “Change Source” capability

Users can now easily update the location of a data source for all queries connected to that data source within a single Excel workbook. Before this update, users had to update the data source location for each of the queries connected to the source. The new solution allows users to do this in a single step.

Within the Data Source Settings dialog (Data > New Query > Data Source Settings), there is a new Current Workbook scope option, which allows users to limit the list of displayed data sources to only those that are used in the current file. From this view, the users can modify credentials, privacy levels and other settings for that data source, or use the Change Source option to modify the location for that data source.

July 2016 updates for Get Transform in Excel 2016 7

Advanced Filter Rows dialog mode within the Query Editor

We’re introducing a new mode in the Filter Rows dialog within the Query Editor. This new mode allows users to add multiple filter clauses within a single Filter Rows step (before this update, only 1–2 clauses were allowed) and also combine filter clauses based on multiple columns (before, all clauses were applied to a single column).

July 2016 updates for Get Transform in Excel 2016 8

Inline Input controls for Function invocation within the Query Editor

We improved the inline preview for functions within the Query Editor dialog to allow input values for function invocation. This new Enter Parameters section in the inline function preview is available for both “out of the box” functions (M Engine functions) as well as user-defined functions.

July 2016 updates for Get Transform in Excel 2016 9

Support for reordering Query Steps within the Query Editor by using drag and drop gestures

We now support reordering of Query Steps within the Query Editor by using drag and drop gestures. This can be done for each query via the Applied Steps section in the Query Editor.

July 2016 updates for Get Transform in Excel 2016 10

Date picker support for input Date values in Filter Rows and Conditional Columns dialogs

In this update, users can now leverage a Date picker control to provide input Date values in the Filter Rows and Conditional Columns dialogs.

July 2016 updates for Get Transform in Excel 2016 11

New context menu entry to create new queries from the Queries pane within the Query Editor

You can now add a new query in the Queries pane within the Query Editor when you right-click in the background of the pane or in a query group node. This allows a more intuitive experience for adding new queries when working in the Query Editor pane. Also, when you right-click a query group, the new query is added to the selected group.

July 2016 updates for Get Transform in Excel 2016 12

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 take advantage of these updates by downloading the latest Power Query for Excel add-in.

—The Excel team

Top
29 comments
  1. I couldn’t see the new connectors (SAP HANA, SharePoint Folder) in the add-in for Excel 2013. Can someone check if it’s a bug?

    • Hi Di Ming

      Same here – SAP Hana and Share point folder Not there is Excel 2016 after the latest update (Version 1606 (Build 7070.2033)) nor in the Power Query Add-in 2.35.4399.761

      Hi PQ Team
      My request – STOP all updates for about 3 Months
      – Use this time to bring all three versions of the same plane – viz –
      Get Data – Power BI Desktop
      Power Query Addin for 2010/2013
      Get and Transform – 2016

      Then release an update – This way we get consistent experience across products – Or else we get what used to be called as “.net Hell”

      Best Regards
      Sam

      • Hi Sam,

        Power Query add-in for Excel 2013 and Excel 2016 Get & Transform capabilities are updated monthly and are synced in terms of Power Query version.
        Power BI Desktop has a slightly advanced version of Power Query, which is usually finds its way into the Power Query add-in for Excel 2013 and Excel 2016 Get & Transform within 1-2 months period.

        As I answered Di Ming, can you please check your Office plan under File > Account > Product Information?

        SAP HANA and SharePoint folder connectors available only for enterprise Office plans (e.g., Office 365 ProPlus) as these are typical corporate sources. You can also learn more about availability of different BI features in Excel here:
        https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

        Guy
        – Excel Team

    • I also do not see the new connectors in the add-in for Excel 2013.

      • Hi Ivan,

        New connectors are available as a part of Office 365 subscription. Power Query add-in for Excel 2013 includes all the mentioned connectivity and transformation improvements, except of the new connectors. I will make sure to clarify this in the future blog posts.

        Guy
        – Excel Team

    • Hi Di,

      Can you please check your Office plan under File > Account > Product Information?

      We made SAP HANA and SharePoint folder connectors available only for enterprise Office plans (e.g., Office 365 ProPlus) as these are typical corporate sources. I will make sure to add an explicit note for this in the future blog posts.

      You can learn more about availability of different BI features in Excel here:
      https://blogs.office.com/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

      Guy
      – Excel Team

      • Hi Guy,

        It is Microsoft Office Professional Plus 2013, this is my enterprise version. It should have all features.

        • Thanks Di Ming,

          New connectors are available as a part of Office 365 subscription. Power Query add-in for Excel 2013 includes all the mentioned connectivity and transformation improvements, except of the new connectors. I will add a note for this in the future blog posts.

          Guy
          – Excel Team

    • Hi Ce Fuller,

      Currently we have no immediate plans to support it, however this is something that several customers have been asking for. The best way to influence and promote this request will be to vote for this feature on Excel Voice, which will help us to prioritise it better.

      https://excel.uservoice.com/

      Guy
      – Excel Team

  2. I too am baffled by this PQ update. The SharePoint Folder Connector would be much appreciated – but where is it? I’m running Office Pro Plus 15.0.4841.1002. I installed the PQ update per the links in this article. I do not find the SharePoint Folder Connector. Suggestions?

    • Hi Brent,

      These new connectors are available as a part of Office 365 subscription. Power Query add-in does include all the connectivity and transformation improvements mentioned in this blog, except of the new connectors. I will make sure to clarify this in the future blog posts.

      Guy
      – Excel Team

  3. Hi, great update, but when will you lift the 2000 lines load restriction?

    • Yes, I concur. This limit is a significant pain and seems pretty pointless.

      • Libor, Zachary,

        I assume you’re referring to the 2,000-row limit when retrieving data using the Salesforce Reports connector. If something else, please clarify.

        Regarding the Salesforce Reports 2,000 row-limit, as much as we would *love* to remove this limitation, it is not an Excel / Power Query limitation but a general Salesforce API restriction. Please make sure to share this feedback with Salesforce as this is fully in their control. In fact, once/if they remove this API limit, there wouldn’t be any product change required on the connector to pull the entire dataset, as there’s not any explicit limit defined on our side.

        Thanks,
        M.

  4. Thank you for the update! but are you going to have R script integration like the “R.Execute” in power bi query….

    • Hi

      We are currently looking into what it means to bring this functionality into the Excel Power Query setup, however I cannot yet refer to any specific time at this stage.

      thanks

      Eli
      -Team Excel

  5. Hello,

    when I use Get & Transform in Excel 2016 Standard SPLA, I´m able to create query, see the preview, but when I’m finishing the process and would like to get the data to cells, Excel show me error message “initialization of data source failed, …” – I’ve tried more sources from limited commands in Get & Transform in Standard suite including simple link to Excel file in same folder… everything ends with error… in cells is shown only few rows (<10)

    When I do all this in Excel 2016 Professional Plus, everything is OK, no errors, result 250000 rows inserted in cells rather quickly…

    I'm using Windows Server 2012 R2 std 64b CZE, normal file system…

    Is Get & Transform generally usable in Standard version? What are conditions for use and what can be cause of initialization error?

    Thank for help
    Petr

  6. I have a problem: “Change Source” option in “Data Source Settings” is not available. I have several queries linked to a data source of an intranet folder company where I work

    • Hi Linelson,

      Can you please share your Excel version (File > Account > About Excel) and Power Query version (Data > New Query > Query Options > Diagnostics)?

      Guy
      – Excel Team

      • Hi!

        Excel 2013 (15.0.4841.100) MSO (15.0.4841.1000) 64 Bits
        Microsoft Office Professional Plus 2013

        Power Query: 2.35.4399.761 64 bits

        Best Regards

        • Hi Linelson,

          Is the button invisible or disabled? If it’s disabled, it’s because Change Source is currently only supported for database data sources. The next update will add support for most other sources like files and folders.

          Thanks,
          Alejandro

Comments are closed.