Share via

Power Query becomes smarter and more powerful

  • Article

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Note

These release notes describe functionality that may not have been released yet. To see when this functionality is planned to release, please review What’s new and planned for Common Data Model and Data Integration. Delivery timelines and projected functionality may change or may not ship (see Microsoft policy).

One of the major trends within the self-service data preparation space is smart data preparation. It brings the power of AI into the hands of the data analyst, democratizing access to AI algorithms to enrich and enhance data, whether that is automatic (such as built-in recommendations) or by means of user interaction (such as calling in to AI/ML functions).

Over the last 6-12 months, Power Query added a lot of smart data preparation capabilities to its core experiences, including:

  • By example data extraction capabilities: Allows users to extract data from existing table columns or from HTML pages. This is based on letting users specify a set of sample output values, having Power Query infer the users' intent, and then extracting the remaining values by applying AI algorithms.

  • Fuzzy merge: Allows users to easily combine data from multiple tables using fuzzy matching algorithms instead of strict matching.

  • Data profiling: Helps users easily identify error, empty, and outlier values within their Power Query Editor data previews.

  • Built-in browsing and invocation experiences in Power Query Online: Uses Azure Machine Learning models and Azure Cognitive Services functions as regular data preparation steps in Power BI dataflows. This brings together the out-of-the-box Cognitive Services capabilities built in to the Azure cloud platform, as well as the ability to consume Azure Machine Learning models created by an organization’s data scientists, and shared with data analysts for consumption.

    Data profiling image

In the upcoming months, we will continue building on this foundation to make Power Query’s smart data preparation capabilities even more powerful, helping data analysts achieve more from their data with even less effort.

High enterprise value connectors become generally available for Power BI customers

By September 2019, many of the recently shipped Public Preview capabilities for enterprise-grade connectors in Power BI will become generally available, including:

  • SAP BW single sign-on (Kerberos) in Power BI service (via on-premises data gateway)
  • SAP HANA single sign-on (SAML) in Power BI service (via on-premises data gateway)
  • PDF connector
  • Essbase connector
  • IBM DB2 DirectQuery
  • PostgreSQL folding over native query
  • Power Platform Dataflows

In addition, the following connector will go into Public Preview:

  • AtScale connector

Additionally, Power BI is enabling a new Public Preview capability on top of the SAP HANA and BW connectors based on enterprise customer feedback, allowing report consumers to modify SAP HANA/BW variable values within the PowerBI.com report consumption experiences.

Power Query Online adds several new data connectivity and preparation capabilities

Power Query is available as a Windows/Desktop embeddable component (currently integrated with Power BI Desktop, Excel Desktop, and SQL Server Data Tools) as well as a web-based, Azure-hosted service (currently integrated with Power BI dataflows, Common Data Service for Apps, and Microsoft Flow).

Over the next six months, we'll add the following data connectors to Power Query Online:

  • Amazon Redshift
  • Impala
  • Vertica
  • Teradata
  • MySQL
  • PostgreSQL

Increased support for developers on the Power Query platform

With the general availability of certified connectors and custom connector development for Power BI, the Power Query platform has made the same tools that we use to develop connectors available to developers around the world.

Over the next six months, we will continue to expand our offerings to developers and the ability for anyone to connect their data to the Power Query platform.

Developers have asked us to support a way to deliver connectors that have been signed with their certificate, rather than going through Microsoft’s process. We will be introducing a method to enable developers and users to use connectors signed by third parties.