Back
Excel

11 updates to Power Query

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

This month’s Power Query update includes 11 new or improved features including:

  • Support for all Excel 2013 Desktop SKUs
  • OData V4 support
  • Unified Options dialog
  • Option to disable the Native Database Queries prompt
  • Support for custom ADFS Authentication Services
  • Updated Facebook connector due to Facebook API changes
  • Support for Fixed Decimal Number type
  • Alternate Windows Credentials
  • Online Search is now Data Catalog Search and in a new ribbon location
  • New transformations
  • Additional performance improvements for loading medium and large datasets.

button (3)

Continue reading below for more details about each feature.

Support for all Excel 2013 Desktop SKUs

With this update, we’re making Power Query available to all Excel 2013 Desktop SKUs. There are some differences in features, depending on what SKU users are running:

  • Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone: Full Power Query feature set.
  • All other desktop SKUs—Full Power Query feature set, except the following data connectors: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects and Salesforce.

Power Query detects your Excel 2013 SKU when launched and enables the appropriate set of features.

OData V4 support

We’re adding support for OData V4 feeds. You can just use the existing OData Feed connector, which will now also accept feeds built using the latest OData version.

Unified Options dialog

We have combined Workbook Settings and Options into a single dialog, organized by scope (current file versus global) and categories for easier navigation.

11 updates to Power Query 1

Option to disable the Native Database Queries prompt

One useful capability when connecting to databases is the ability to provide a custom SQL statement. This is helpful for customers who have complex SQL queries to pull data for their reports and want to get started with Power Query. However, there is a potential risk for these queries to contain malicious SQL code that could delete or modify content in a database when executed. Because of this risk, we have an existing security prompt whenever users try to run a native database query outside of the data source dialogs. It turns out that lots of customers are using this capability within Custom Columns or similar scenarios, so they can dynamically build and execute native database queries. The downside is that they would get prompted for approval of every distinct SQL statement, which would make the experience very inconvenient.

To enable these customers to achieve their scenarios, we’re introducing an option to disable Native Database Query security prompts. However, please beware of the potential risks mentioned above before using. You can find this option within the Options dialog, under Global > Security.

11 updates to Power Query 2

Support for custom ADFS Authentication Services

With this update, we added support for using custom ADFS authentication endpoints through our Organizational Account credential type. This allows access to data sources that require ADFS authentication such as some on-premises instances of Dynamics CRM. After Power Query is registered by your admin, you will be able to approve a custom endpoint when prompted for access. You can also manage the list of already-approved endpoints within the Options dialog, under Global > Security.

11 updates to Power Query 3

Updated Facebook connector

As of April 30th 2015, Facebook expired v1.0 of its Graph API. The Graph API is what Power Query uses behind the scenes for the Facebook connector, allowing you to connect to your data and analyze it. This expiration means some changes in the Facebook connector as it currently exists. The most important difference is the set of permissions we’re able to leverage and the data those permissions return. For example, Friends Lists and News Feeds are commonly used permissions that are now changed or inaccessible.

Queries built before April 30th 2015 may no longer work or return less data. After April 30th, Power Query leverages v2.2 in all calls to the Facebook API. You’ll likely need to re-authenticate to approve the new set of permissions. More details on the change in the Facebook API are available here.

Support for Fixed Decimal Number type

We have added support for Fixed Decimal Number type. This new type can be found in the Data Type drop-down menu under Home and Transform tabs in the Query Editor, as well as in the Change Type column context menu.

Alternate Windows Credentials

Added an option to use Alternate Windows Credentials (rather than current user) to the Windows credentials option in the Credentials dialog.

11 updates to Power Query 4

Online Search is now Data Catalog Search and in a new ribbon location

We renamed Online Search to Data Catalog Search and moved it from the Get External Data group to the Power BI group on the Power Query ribbon tab.

11 updates to Power Query 5

New transformations

We continue making incremental improvements to the set of transformations supported in the Query Editor. This month, we added the following new transformations:

  • Remove Blank Rows.

11 updates to Power Query 6

  • Median Operation available for Group By and Aggregate Column.

11 updates to Power Query 7

  • Convert DateTimeZone value to Local Time.

11 updates to Power Query 8

Performance improvements

In addition to all the functional improvements described above, we also made Power Query faster when loading medium and large datasets into your Excel Workbook. Your queries will take approximately 20 percent less time to load than what they used to take with last month’s update.

That’s all for this month. As mentioned previously, we’re making lots of incremental improvements to Power Query and we hope that you find it better with every new monthly update. Please continue sending us feedback using our “Send a Smile/Frown” feature, or by voting for what you’d like to see next.

button (3)

—Miguel Llopis, program manager on the Power Query team

———————

Power Query for Excel is available with an Office 365 subscription, Office 2010 Professional Plus with Software Assurance, Office 2013 Desktop SKUs or Excel 2013 Standalone.  Download the add-in and learn more about getting started.

Learn about all the powerful analytics and visualization features in Excel and take your analysis further by sharing and collaborating on business insights with colleagues using Power BI.

Follow us: Facebook and Twitter

Top

Join the conversation

4 comments
  1. Hi,
    I have an issue with this latest update, when working together with PowerPivot.

    Here’s my setup: Excel 2010 64bits + PowerQuery_2.22.4007.242 x64 + PowerPivot for Excel SP2 x64

    When trying to refresh an existing excel that has PowerPivot use a PQ data source (via the Existing Connections menu item), I get a refresh error. I have been able to pin down the error to the following:

    Every time I **SAVE** (not before saving) the workbook, the EmbeddedMashup connection sting of PQ changes (with PQ 2.22). This did not happen with PQ 2.21. With this happening, the PowerQuery refresh breaks.

    Example:

    BEFORE: Working: Excel with PowerQuery 2.21

    Workbook PowerQuery Connection:
    Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(7bd72702-510c-4616-a072-c970477e94c8)$;Location=Query1; (…. rest of string does not change )

    PowerPivot data connection:
    Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(7bd72702-510c-4616-a072-c970477e94c8)$;Location=Query1; (…. rest of string identical )

    The two “$EmbeddedMashup” strings were identical, and saving the Workbook did not affect them. Nothing strange.

    WITH PQ 2.22 (BREAKS POWERQUERY REFRESHES BECAUSE EMBEDDEDMASHUP CHANGES WITH EVERY SAVE)

    Workbook PowerQuery Connection: (does not change at all)
    Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(7bd72702-510c-4616-a072-c970477e94c8)$;Location=Query1; (…. does not change )

    PowerPivot data connection:

    Before saving: Original (PowerPivot refreshes work)…
    Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(7bd72702-510c-4616-a072-c970477e94c8)$;Location=Query1;

    After 1 save: (PowerPivot refresh does NOT WORK anymore): EMBEDDESMASHUP CHANGES!
    Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(5bafbf30-bfd5-47e0-bdc8-1025438c1992)$;Location=Query1

    After saving again: (PowerPivot refresh does NOT WORK anymore, embeddedmashup changes again)
    Provider=Microsoft.Mashup.OleDb.1;Data Source=$EmbeddedMashup(fd8cb919-e5e5-4f7a-8d59-cc658af29ccd)$;Location=Query1

    … etc

    This breaks compatibility between PowerQuery and PowerPivot. It all worked well with v 2.21. Could you please fix the bug?

    Workarounds I’ve found:
    – Downgrade to v. 2.21
    – Alternatively, copy the whole connection string from PQ to PP. It will make refreshing work, until you save the workbook and it breaks again….

    Thanks for all – and by the way, it is really great that you have included PQ in general O365!

    Best regards

    Martin

    • I am having this issue also!!! It is so frustrating. My guess is because I did not create the workbook 100% in 2.22 that is doesn’t work??? I upgraded from 2.18 to 2.22, added some queries, then broke my connection strings from staying static on saving Excel workbook (not changing PQ queries at all).
      I’m having to update 14+ queries in quite a large model to even get it to refresh. PLEASE HELP! When is the new release? Isn’t it monthly?

  2. I am using PQ 2.22. How do I downgrade to 2.21? The Microsoft site seems to always bring up the latest code and I am trying to find a way to revert to 2.21.

    • Hi all,

      Sorry for the inconvenience that this issue has caused to many of you. We have a fix for this issue coming in our next update, which we plan to release later this week (Wednesday morning, PST time).

      Despite fixing this issue, I shall point out that this scenario is not officially supported by our team. In Excel 2010, both Power Query and Power Pivot are “external” add-ins and thus the integration between them is very fragile. We cannot guarantee that the scenario above will work reliably and, in fact, we’re not testing this level of integration with Power Pivot in Excel 2010 (so unintentional regressions like this one can happen).

      Thanks,
      M.

Comments are closed.