Data Connectors for Power BI enables users to connect to and access data from your application, service, or data source, providing them with rich business intelligence and robust analytics over multiple data sources. By integrating seamlessly into the Power Query connectivity experience in Power BI Desktop, Data Connectors make it easy for power users to query, shape and mashup data from your app to build reports and dashboards that meet the needs of their organization.
Data Connectors are created using the M language. This is the same language used by the Power Query user experience found in Power BI Desktop and Excel 2016. Extensions allow you to define new functions for the M language, and can be used to enable connectivity to new data sources. While this document will focus on defining new connectors, much of the same process applies to defining general purpose M functions. Extensions can vary in complexity, from simple wrappers that essentially just provide "branding" over existing data source functions, to rich connectors that support Direct Query.
Please see the Data Connector technical reference for more details.
Note: The steps to enable extensions changed in the June 2017 version of Power BI Desktop.
- Install the Power Query SDK from the Visual Studio Marketplace
- Create a new Data Connector project
- Define your connector logic
- Build the project to produce an extension file
- Create a
[My Documents]\Microsoft Power BI Desktop\Custom Connectorsdirectory - Copy the extension file into this directory
- Enable the Custom data connectors preview feature in Power BI Desktop (under File | Options and settings | Custom data connectors)
- Restart Power BI Desktop
Power BI Desktop users can download extension files and place them in a known directory (steps described above). Power BI Desktop will automatically load the extensions on restart.
We are hard at work on Office Store integration to make it easy for users to discover and install data connectors you build. During this preview phase, developers interested in distributing their connectors for use with Power BI can contact us at DataConnectors (at) microsoft.com.
- Data Connector Technical Reference
- M Library Functions
- M Language Specification
- Power BI Developer Center
- Data Connector Tutorial
The following code sample defines a simple "Hello World" data source. See the full sample for more information.
section HelloWorld; [DataSource.Kind="HelloWorld", Publish="HelloWorld.Publish"] shared HelloWorld.Contents = (optional message as text) => let message = if (message <> null) then message else "Hello world" in message; HelloWorld = [ Authentication = [ Implicit = [] ], Label = Extension.LoadString("DataSourceLabel") ]; HelloWorld.Publish = [ Beta = true, ButtonText = { Extension.LoadString("FormulaTitle"), Extension.LoadString("FormulaHelp") }, SourceImage = HelloWorld.Icons, SourceTypeImage = HelloWorld.Icons ]; HelloWorld.Icons = [ Icon16 = { Extension.Contents("HelloWorld16.png"), Extension.Contents("HelloWorld20.png"), Extension.Contents("HelloWorld24.png"), Extension.Contents("HelloWorld32.png") }, Icon32 = { Extension.Contents("HelloWorld32.png"), Extension.Contents("HelloWorld40.png"), Extension.Contents("HelloWorld48.png"), Extension.Contents("HelloWorld64.png") } ];
Data Connectors allow you to create new data sources, or customize and extend an existing source. Common use cases include:
- Creating a business analyst friendly view for a REST API
- Providing branding for a source that Power Query supports with an existing connector (such as an OData service, or ODBC driver)
- Implementing an OAuth v2 authentication flow for a SaaS offering
- Exposing a limited/filtered view over your data source to improve usability
- Supporting different authentication modes when creating a Power BI Content Pack
- Enabling Direct Query for a data source via an ODBC driver
Currently, Data Connectors are only supported in Power BI Desktop.
Warning: Compatibility issue with Microsoft Analysis Services Projects extension and SQL Server Data Tools
We have a known compatibility issue with Analysis Services Tabular projects. You may encounter assembly loading exception dialogs under the following conditions:
- You are using a Tabular model project with compatibility level of 1400
- While using a Tabular project, you use the Import from Data Source experience or refresh an existing data source
- You are using a Data Connector project
- You try to use both projects in a single Visual Studio session
The errors occur due to a version mismatch between assemblies that are shared by both projects. This results in one of the following errors:
- Could not load type 'Microsoft.Mashup.Engine.Interface.MashupFileExtension' from assembly 'Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.
- Could not load file or assembly 'Microsoft.ProBI.MashupLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.
You will see a different error depending on which project type is used first within a single Visual Studio session. Using the projects from different Visual Studio instances should not cause conflicts. Once you see the error, restarting Visual Studio should make it go away (until both projects are loaded again).
We are working to address this issue upcoming updates for both extensions.
Data Connectors are currently in preview. We plan to incrementally roll out a number of enhancements prior to general availability, including:
- File extension changes (.mez to .pqx)
- Improved tracing and diagnostics for developing Direct Query capable connectors
- Versioning of extensions, and support for dependencies
- Improved support for Library extensions (for reusable utility functions)
- Integration and support for API Connectors for Microsoft Flow and PowerApps
- Support for Scheduled Refresh via the On-Premises Data Gateway
- Integration with the Office Store
- Development experience improvements
Please report issues and feature requests through our Github issues page.