A custom connector in Power BI is essentially a code segment written in M that queries information from a data source. This M code typically makes requests to an API and then reformats the data retrieved from the API into a usable structure. Power BI custom connectors also feature a visual interface to select the tables you wish to import, similar to a “Get Data” menu. Below, you will find a video showcasing the custom connectors developed for our clients at Versich.
To create this type of visual interface, you’ll need to utilize the Power Query SDK within Visual Studio. For official guidelines, refer to the Microsoft documentation on this topic. To check if creating a custom connector in Power BI for your data source is feasible, you can search for information like “Xero API documentation” or “Anaplan API documentation.” If you find official API documentation, then developing a custom connector in Power BI is possible.
The Process of Building a Custom Connector in Power BI
When connecting to APIs through Power BI, it’s essential to grasp the API’s authentication methods, the structure of requests and responses, and any specific parameters necessary for retrieving data. Let’s explore this process step by step.
1. API (Application Programming Interface)
An API consists of rules and protocols that enable various software applications to interact and communicate. Each API establishes the methods, data formats, and standards that developers utilize when crafting integrations.
2. JSON
When you send a request to an API, it typically responds with data formatted in JSON. JSON is used by APIs to consistently transmit and interpret data. In the realm of Power BI, we refer to it as nested data. The fundamental elements in JSON include objects, keys, and values.
2a. Objects
An object is a collection of key-value pairs that are unordered and enclosed in curly braces {}. Every key is represented as a string followed by a colon :, leading to its corresponding value.
2b. Arrays
An array consists of an ordered list of values, enclosed in square brackets [].
2c. Values
In an array, values can take on various data types, including objects, arrays, numbers, strings, booleans, or null. Values in JSON can be strings, numbers, booleans (true/false), null, objects, or arrays.
Power BI Custom Connector REST API
To construct a Power BI custom connector using a REST API, we should opt for the "Get data from the Web" choice. Each data source has its unique authentication mechanism, allowing users to access the data. The four commonly used authentication methods are:
Anonymous (No authentication)
Basic (username and password)
Bearer tokens
OAuth 2.0
Connect to an Open API (No Authentication)
Let’s explore how to retrieve data from an API that doesn’t require authentication. Open Power BI Desktop and click on ‘Get data.’ Choose the WEB connector from the options and input the API URL. For this demonstration, we will use datausa.io's open API, a platform offering free APIs for testing and data consumption.
Initially, Power BI will request the authentication type for the first connection. The basic options available are:
Anonymous - Applicable to open data sources accessible by everyone.
Windows - This option verifies the data source using the credentials of the logged-in Windows user.
Basic - This is relevant when users have a username and password for authenticating the data source.
Web API - This utilizes a secret key or token for authentication.
Organizational Account - This is suitable for data sources accessible via a Microsoft account (O365).
As we are engaging with an open data source, we will select the Anonymous authentication option. It may take a moment to execute the API query, and then the data will be displayed in Power Query. Since the information is in JSON format, it must be converted into table format.
Select the list from the data column.
Power Query will unveil the list of items. Choose “Convert to table” from the top left to change it to a table format.
Expand the columns by clicking the column expand button and selecting the required columns. After you click OK, the data will be formatted in the necessary table structure.
Connect to an API That Requires a Bearer Token
Some APIs necessitate authentication through a bearer token. Our Power BI custom connector can transmit a provided bearer token in the headers to authenticate and access the API's data source. Later in this article, I’ll explain what a header is. Ensure that the bearer token is valid and allows access to the data source.
There are two methods to pass a bearer token to Power BI: Option 1: Generate Token in Postman or Another Application This is suitable if your tokens have a long expiration period. Some tokens last for a year while others may only last for 20 minutes, depending on the particular API in question. I’ll illustrate how to use a bearer token to authenticate with an API.
Open Power BI Desktop and click on Get Data. Select the WEB connector and enter your URL.
Select advanced, then provide the API URL in the URL Parts field. Add Authorization to the header and supply the token formatted as (Bearer Token), then click OK to continue.
If the header is valid and grants access to the data source, it will display the list in Power Query. As the token expires after a certain time, we must generate a new token before each call to update the latest data in Power BI.
If your token has a short lifespan, you can automate the token generation by writing a function in Power BI that produces the token with each ‘GET call.’ Below is an example of how this works.
Option 2: Generate Token Dynamically in Power BI. This applies when you prefer to have the token dynamically generated on each call rather than passed manually. The example below entails an API access through a username and password. Upon successfully logging in, the API returns a bearer token.
Open Power Query and select New source -> Blank Query.
Paste the following code into the advanced editor, replacing the placeholder details with your URL, username, and password. Click OK to advance.
```m let url = "URL", headers = [#"Content-Type" = "application/json"], postData = Json.FromValue([username="", password=""]), response = Web.Contents(url, [Headers = headers, Content = postData]), Data = Json.Document(response), access_token = Data[token] in access_token ```
This code will authenticate and generate a token key. Once the token key is available, we can apply it for authentication by converting the query into a function.
Add () at the start in the advanced editor and click OK. This will transform the query into a function.
Open the dataset and replace the token key with the name of the function, ensuring there is a space between Bearer and the function name.
Now, each time this dataset is loaded, Power BI will generate a new token using the function and pass it as a key to the authorization.
Conclusion
This article has outlined fundamental concepts for creating a custom connector in Power BI, establishing a Power BI custom connector folder, and utilizing OAuth2 in a custom connector. I trust this information aids in grasping how the creation of a custom connector in Power BI is structured.
