microsoft / dataconnectors

Data Connector SDK and samples for Power Query and Power BI

Update 10/18/2022: A new version of the Power Query SDK has been released and is currently in Public Preview. We welcome you to try out this new SDK from the Visual Studio Code Marketplace and provide feedback, report bugs and share suggestions around this new SDK. All future investments in regards to the Power Query SDK will go towards the new SDK. You can also join the new discussions section in the new Power Query SDK GitHub repository where you can interact with other members of the community and ask questions around the development of custom connectors. Below, you'll find a list with all the links to the resources of the new Power Query SDK.

• New Power Query SDK extension for Visual Studio Code
• GitHub repository for the new Power Query SDK
• Quickstart guide for the new Power Query SDK in Visual Studio Code

Getting Started with Data Connectors

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 Power Query Connector Developer Reference for more details.

Quickstart

1. Install the Power Query SDK from the Visual Studio Marketplace
2. Create a new Data Connector project
3. Define your connector logic
4. Build the project to produce an extension file
5. Copy the extension file into the [Documents]\Power BI Desktop\Custom Connectors directory
6. Open Power BI Desktop

Note, to load extensions during development, you will need to lower the security level for extensions in Power BI Desktop to enable loading unsigned/uncertified connectors.

1. Go to File | Options and settings | Options
2. Go the Security tab
3. Under Data Extensions, select Allow any extension to load without warning or validation
4. Restart Power BI Desktop

Gateway Support

The Power BI On-Premises Gateway now supports loading custom connectors. Please see the technical documentation for more information, and the TripPin Sample for an example of how to add gateway support to your connector.

Distribution of Data Connectors

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.

Please see the Connector Certification documentation for details on the certification process and requirements.

Additional Links and Resources

• Data Connector Technical Reference
• M Library Functions
• M Language Specification
• Power BI Developer Center
• Data Connector Samples

The recording of the Creating a Custom Data Connector session from the Microsoft Data Insights Summit 2017 can be found by clicking the image below:

Hello World Sample

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") }
];

What You Can Do With a Data Connector

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

Additional Information

The dataset provided with the Power Query SDK Test Framework is based on datasets from the NYC Taxi and Limousine Commission and provided under the CDLA-Permissive-2.0 license. Please see the following links for more information:

• Original dataset location
• Original terms of use