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.
Key Takeaways
  • Power BI custom connectors are the modules that enhance the Power BI data connectivity capabilities, allowing it to connect with data sources that are not natively supported.
  • There are two categories of custom connectors, i.e., Non-certified and certified. Non-certified custom connectors are vendor-distributed and require security configuration. Certified custom connectors are certified and distributed by Microsoft but developed by third-party vendors.
  • Custom connectors enable you to connect with various data sources and APIs across multiple avenues, such as on-premises data gateway, cloud data sources, or legacy systems.
  • The custom connectors are built using M language and Power Query SDK.
  • To build a custom connector, you need to have a good understanding of M language and technical awareness of security configurations.

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.

Power BI Custom Connector - Step 6

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.

Power BI Custom Connector - Example 3 - Step 2

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.

Power BI Custom Connector - Example 3 - Step 4

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. 

Power BI Custom Connector - Example 3 - Step 5

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)

1. Can I share my Power BI custom connector with others?

Yes, you can share your Power BI custom connector with others. You can follow the steps outlined below to share your connectors:

Step 1: Share your custom connector file (.pq, .pqx, .m, or .mez file) with others.

Step 2: Users can copy the file into their Power BI custom connector folder path, i.e., [Documents]\Microsoft Power BI Desktop\Custom Connectors folder. In case such a folder is non-existent, then they need to create such a folder.

Step 3: Users will have to adjust the data extension security settings in their Power BI Desktop by navigating to File – Options and settings – Options – Security.

Step 4: They need to select (Not Recommended) Allow any extension to load without validation or warning option under Data Extensions and then select OK to use the custom connectors.

2. Are there any limitations to custom connectors in Power BI?

While the custom connector is a valuable tool, it comes with certain limitations, as highlighted below:

• For non-certified custom connectors, adjustments to Power BI security settings are required, which can ignore the privacy levels and send your credentials over HTTP. 
• Custom connectors require you to build a new data connector using M language and maintain it on an ongoing basis. It will add cost, resources, and technical expertise.
• You will need additional configurations to share the custom connectors with others, unlike certified/native connectors.
• If you are using DirectQuery mode, you can only use one custom connector data source as multiple custom connector data sources don’t work with Power BI custom connector DirectQuery.

3. Can I use a custom connector with Power BI Online or Power BI Service?

Yes, you can use a custom connector with Power BI Online or Power Service. While custom connectors are fully supported in Power BI Desktop, for using it in Power BI Online or Power BI Service, you will have to enable and configure it with an on-premises data gateway.

4. Can I use a custom connector with the Power BI Report Server?

No, you can’t use a custom connector with Power BI Report Server as it is not supported.

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 –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *