Power BI Custom Connector

What is a Power BI custom connector?

Power BI custom connectors allow you to connect with different applications, services, and data sources and provide data access to these platforms. You can build Power BI custom connectors and use them to connect to various data sources that are not yet supported and APIs across multiple avenues such as on-premises data gateway, cloud data sources, or legacy systems.

Power BI custom connectors are built using the M language that provides flexibility to define new functions, new connectivity to data sources, and choice of wrappers to implement simple to complex logic. Additionally, you will also need Power Query SDK to create these connectors. Some broad categories of Power BI custom connectors include

Non-certified custom connectors

• Microsoft does not release these connectors, and they are distributed mainly by vendors.
• They can be small business-critical APIs or extensive industry-centric services.
• You may have to adjust the security settings for such connectors.

Certified custom connectors

• These connectors are created by third-party developers but certified and distributed by Microsoft.
• These connectors are available in Power BI Desktop in the list of connectors that appear in the Get Data screen.
• You don’t have to adjust the security settings for such connectors.

How to create a Power BI custom connector?

Power BI custom connectors enable you to establish connections and perform data querying, data operations, and transformation from your custom apps to create reports and dashboards. Before you start creating custom connectors, there are some prerequisites that you need to understand. These include:

• Familiarity with M language specifications and library functions
• Power BI Developer Center
• Power Query SDK
• Power BI custom connector GitHub repository

To create a custom connector in Power BI, follow the instructions outlined below:

Step 1: Download the Power Query SDK from the Visual Studio Marketplace and install it on your machine

Step 2: Create a new project in Visual Studio and provide the name of the project

Step 3: Configure connector logic using M language to define the wrapper or new connector to enable connectivity to supported data sources. There are various usages of the custom connectors that include:

• Use a REST API in Power BI for simple view creation.
• Providing branding for a source that Power Query supports with an existing connector (such as an OData service, or ODBC driver).
• Branding a source that Power Query supports using an existing connector such as an OData service or ODBC driver.
• Apply Power BI custom connector OAuth2 flow for a SaaS model.
• Allow Power BI custom connector direct query for any data source using an ODBC driver.
• Create a reusable filtered view over your data source and expose it for effective utilization.

Step 4: Build the project to produce an extension file. These extension files can be .pq, .pqx, .m, or .mez files in your local folder.

Step 5: Copy the extension file created in step 4 into a custom connector folder i.e. [Documents]\Microsoft Power BI Desktop\Custom Connectors folder. If no such folder exists, then you will need to create one.

Step 6: Launch your Power BI Desktop application. Navigate to File – Options and settings – Options – Security. Select (Not Recommended) Allow any extension to load without validation or warning option under Data Extensions. Select the OK button.

Step 7: Restart the Power BI Desktop to use the custom connector you have configured using the steps above.

Examples

In this section, we will discuss some of the use cases of Power BI custom connectors.

Example #1

In this example, we will demo a simple example of creating a basic connector that acts as a data source extension and can be loaded in Power BI Desktop. Broadly, this connector provides the below output.

• Accepts the input text parameter in the export function
• Defines a data source that uses anonymous authentication, uses string texts for localization, and declares UI metadata to display the extension in Power BI Get Data Dialogue 

The sample code to create the custom connector is provided below:

//Section statement
section HelloWorld;
//Data source definition and publish
[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;
//Authentication record declaring that anonymous authentication is used for this data source
HelloWorld = [
Authentication = [
Implicit = []
],
Label = Extension.LoadString(“DataSourceLabel”)
];
HelloWorld.Publish = [
Beta = true,
ButtonText = { Extension.LoadString(“FormulaTitle”), Extension.LoadString(“FormulaHelp”) },
SourceImage = HelloWorld.Icons,
SourceTypeImage = HelloWorld.Icons
];
//icon sizes with specific png extensions in the build folder
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”) }
];

Once this file is built and copied to the Power BI custom connector folder, you will see it in the Get Data dialog.

Example #2

In this example, we will discuss the use case of creating a custom connector to connect a custom REST API. A Financial Institution has built a custom REST API that captures the client details such as domicile, number of accounts, total balance, etc., without exposing any client-sensitive information. You can build a custom connector to connect with this REST API to pull out the exposed information from this API for your reporting requirements in Power BI and create your dashboard.

By building this connector, you can enforce Power BI custom connector oauth2 or use an API key or any other authentication mechanism. 

To implement this connector, you may follow the instructions outlined below.

Step 1: Create a basic REST connector to call out the REST API using Web.Contents.

Step 2: Build your code transformation logic using the M query language. Open the file extension in Power BI Desktop and build design queries on top of it to curate the output in a user-friendly output.

Step 3: Once your solution is rebuilt, copy the newly created extension file into the custom connector folder and restart the Power BI.

Step 4: Simulate a navigation table using the M language and change the security settings to Allow any extension to load without validation or warning

Now, you should be able to use the custom connector to fetch data from the REST API.

Example #3

In this example, we will understand the use of custom data connectors to create reports in Power BI Desktop and then use an on-premises data gateway to refresh those reports using Power BI Service.

To enable and use custom connectors in the on-premises data gateway, follow the steps highlighted below:

Step 1: Open your on-premises gateway app and navigate to the Connectors tab.

Step 2: Click on the ellipsis(…) under Load custom data connectors from folder in Custom data connectors to browse for the folder to where the user running the gateway service has access.

The custom connector files available in the folder are automatically loaded by the gateway and displayed in the list of data connectors.

Step 3: Create a data source for your custom connector for your on-premises gateway. 

Step 4: Select the option to allow the use of the custom connector with this cluster on the gateway settings screen on the Power BI Service page.

Step 5: You will see the custom connectors in the available list of data source connections, which you can add to the gateway cluster. Once you have created a new connection, you can use the custom connector in Power BI Service to refresh your Power BI reports. 

Important Things to Note

• There are two broad categories of Power BI custom connectors, i.e., Non-certified and certified. While using Non-certified custom connectors, be careful with the security settings.
• Consider implementing robust error handling in your connector to provision a good user experience.
• Ensure you have put through solid authentication mechanisms to deal with sensitive information and restrict unauthorized access to the data.
• Wherever possible, consider signing the custom connector with a certificate to restrict users with any security configuration settings.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to Power BI Custom Connector. Here we explain how to create certified and non-certified custom connector in Power BI, with examples, and points to remember. You may learn more from the following articles –

• Power BI PowerShell Cmdlets
• Power BI Live Connection
• Power BI Data Lineage