Simpler, smarter transformation and integration of enterprise data with Power Query and Data Integration Platform

  • 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 Summary of what’s new. Delivery timelines and projected functionality may change or may not ship (see Microsoft policy). For detailed information about our products, visit the Common Data Model documentation.

We have simplified common data integration problems in enterprises by innovating in:

  • New Power Query data preparation capabilities.
  • Enterprise-grade connectors.
  • Improvements for Common Data Service data integration.

New Power Query data preparation capabilities

Power Query is being dramatically enhanced with market-leading Smart Data Preparation experiences, including:

  • Data extraction from semi-structured sources like PDF files.
  • HTML pages.
  • Fuzzy-matching algorithms to rationalize and normalize data based on similarity patterns.
  • Data profiling capabilities.

These abilities help to easily identify errors and outliers as part of the data preparation workload in the Power Query Editor.

Microsoft is leveraging its leadership in the artificial intelligence field based on years of investment in several projects within Microsoft Research, and surfacing these Smart Data Preparation capabilities in Power Query, making them easily accessible to millions of business users across different Microsoft products and services (Excel, Power BI, Common Data Service for Apps, and Microsoft Flow).

Intelligent transforms and AI support in Power Query

Business analysts can easily incorporate AI-driven insights with a single-click access to AI-based transforms. Initial capabilities in this area will include sentiment analysis and keyword extraction from natural language text. Additional capabilities such as OCR and image analysis could be added over time.

Analysts and BI Pros will gain access to new out-of-the-box functions and APIs for AI transformations. This facilitates column transformations for sentiment analysis and keyword extraction with a click of a button, or with a single line of script, such as “ApplySentimentIndex([textResponse])”.

The Power Query APIs will also be supported for use during installation and configuration of Power BI applications making use of dataflows. In this instance, batch-processing of the transforms for the column will be directed seamlessly to a Cognitive Services container running as part of a customer’s Power BI Premium capacity.

Analytic and ML extensibility with scripting

We currently support the use of R scripts for custom modeling and visualization in Power BI. These R scripts can also be executed as part of data refresh in the Power BI Service leveraging the on-premises Data Gateway (personal mode) with the option to plug in custom R libraries.

Python has gained widespread adoption among developers and data scientists. It is the de-facto standard for data analysis and AI-related work due to its large support for useful libraries. As the next step, Power BI Desktop is adding support for Python scripting.

Intellisense support for the M formula language

Intellisense support for the M language editing experiences (Advanced Query Editor, Add Custom Column, and Formula Bar) is being added to the Power Query Editor, making users more successful in editing their M code directly, being able to easily find errors, discover M library functions, and understand what parameters are needed. M Intellisense support is among the most requested features from intermediate-to-advanced Power Query users in the last couple of years and remains at the top of the feature asks for Power Query in the Power BI and Excel feature suggestions forums.

In addition to new core capabilities within Power Query, Microsoft is also extending the range of products and services that leverage Power Query. Over the past six months, Power Query Online has been integrated with Microsoft Flow.

Flow support for data filtering and mashup

Microsoft Flow now integrates with Power Query Online, allowing users to “Get Rows using Power Query,” which enables data filtering and mashup as part of the “Get Rows” action on specific connectors, such as SQL Server.

Power Query documentation

Microsoft Docs has added a section for Power Query. These docs include samples, tutorials, how-to guides, and a developer reference for building custom connectors for Power Query. Any developer or end user interested in working in M, the language used in Power Query, will be able to answer many of their questions here.

Power Query community website

A new Power Query community website has been launched. It includes Power Query technology overview and deep dive articles, as well as forums, UserVoice, and community-driven content (blog posts, webinars). This new community focuses on the core Power Query technology capabilities that enable partners to build new connectors and data transformations on top of the platform, and complements existing product-based resources (Excel, Power BI, PowerApps, and more), which cover specific Power Query integrations for end users.

Enterprise-grade connectors

The Data Integration team continues to contribute to enterprise-grade connectors across Power BI, Common Data Service for Apps, PowerApps, Microsoft Flow, and Logic Apps.

For Power BI, Microsoft is releasing significant enhancements to their SAP Business Warehouse connectors (Application Server and Message Server), which put Microsoft Power BI at parity or, in some cases, ahead of any other third-party BI vendors in terms of connectivity to SAP Business Warehouse (BW).

SAP has certified Microsoft’s connectors for SAP HANA and SAP BW. In addition, the SAP HANA connector has been improved to enable large enterprise capabilities, such as Security Assertion Markup Language (SAML) based single sign-on and SSL certificate validation. The SAP BW connector is being significantly enhanced with a new implementation that provides orders of magnitude better performance as well as additional capabilities.

Other connector improvements will include single sign-on support via Kerberos for Spark on-premises and releasing existing connectors as generally available, including HDInsight Spark, Google BigQuery, Spark (non-HDInsight), and more.

There have also been improvements to the connectors within PowerApps, Microsoft Flow, and Logic Apps such as releasing write capabilities for Azure SQL Data Warehouse, numerous Visual Studio Team Services updates including better triggers and support for custom fields, and additional capabilities in the Oracle connector and many more.

The platform continues to evolve and has added new connectors that work consistently across Common Data Service for Apps, Power BI, PowerApps, Microsoft Flow, and Logic Apps, part of an effort to better align customer experiences across the full suite of products.

Improved connectors and import experiences for CDS data integration

In April 2018, Microsoft unveiled a public preview of Common Data Service for Apps. As part of this preview, there were built-in capabilities for business users to import data from a wide range of data sources from Microsoft and third parties in the cloud and on-premises, leveraging a web-based, no-code/low-code Power Query experience that users are already familiar with from Excel and Power BI Desktop.

In the next six months, Microsoft will continue expanding support for data connectors and data transformations in this web-based Power Query experience, including support for business-critical data sources (on-premises and cloud), such as Oracle, Amazon Redshift, Google BigQuery, Impala, and others.

Microsoft is also adding support for custom connectors (based on the Data Connector SDK) in Power Query Online, so existing custom connectors built by partners can also be leveraged for data integration with Common Data Services for Apps and Power BI dataflows.

The user experience for importing data from files in Power Query Online is also being improved by allowing users to upload local files, which is a common request.

Likewise, Microsoft will enable browsing in Power Query for files in popular cloud-based file storage services, such as OneDrive Business/Personal and SharePoint Team Sites.

Certified custom connectors in Power BI Desktop

In April 2018, Microsoft released the first custom connectors in Power BI, leveraging the powerful capabilities of the M language to allow partners to write their own connectors and distribute them to every Power BI user. Now any user can easily access connectors from all their data sources, and vendors can easily write new connectors as their own customers demand, improving Power BI’s role as the best BI platform for both vendors and end users.

In May 2018, this capability was brought in line with the existing Get Data experience, making the experience seamless with existing, out-of-the-box connectors, and enabling the number of connectors available with a single click to increase dramatically.

In October 2018, this capability becomes generally available, and Power BI will regularly deliver new, certified custom connectors to end users. If you want to know more about the connector certification program, check out the Power Query documentation here.