Building a Custom Power BI Connector: A Practical Walkthrough

A custom Power BI connector is, at its core, a block of M code that knows how to talk to a specific API: it sends a request, gets data back, and reshapes that data into something Power BI can present as clean, selectable tables, much like the built-in Get Data experience you'd see for any native connector. This article walks through the underlying concepts, the four common ways APIs handle authentication, and worked examples for both an open API and one that requires a bearer token.

Building the visual table-picker interface for a connector requires the Power Query SDK inside Visual Studio, and Microsoft's own documentation is the authoritative reference for that setup. Before investing time in any of this, it's worth checking whether your target system can even support a custom connector in the first place: search for the platform's name alongside “API documentation”, something like “Shopify API documentation” or “Mailchimp API documentation”. If official documentation turns up, a custom connector is almost always achievable.

The Building Blocks: APIs and JSON

Before writing any M code, it helps to be clear on two underlying concepts that every custom connector depends on.

What an API Actually Is

An API is simply a defined set of rules that lets two pieces of software exchange information with each other. It specifies how a request should be formatted, what data will come back, and what standards both sides need to follow for that exchange to work reliably.

Why JSON Matters Here

Most APIs respond to a request with data formatted as JSON, and Power BI refers to this kind of structure as nested data once it lands in Power Query. Three building blocks make up virtually all JSON you'll encounter:

• Objects: an unordered collection of key-value pairs wrapped in curly braces, where each key is a string followed by a colon and then its value.
• Arrays: an ordered list of values wrapped in square brackets.
• Values: the actual data sitting inside an object or array, which can be a string, a number, a boolean, null, or even another object or array nested inside.

Authentication: How APIs Decide Who Gets In

Connecting to an API through Power BI's Get Data from the Web option means dealing with whatever authentication scheme that particular API uses. Four methods cover the large majority of APIs you're likely to encounter:

• Anonymous: no authentication required at all.
• Basic: a username and password pair.
• Bearer tokens: a generated access token passed along with each request.
• OAuth 2.0: a more involved authorization flow, typically used by larger platforms.

Example One: Connecting to an Open API With No Authentication

Open Power BI Desktop, click Get Data, and choose the Web connector from the list. For this example, picture a public weather-data API, one of the many open services that let you pull current conditions and forecasts for a given city without needing any credentials at all, and enter that API's URL.

On first connecting, Power BI will ask which authentication type to use. The usual options are:

• Anonymous, for open sources anyone can query.
• Windows, which authenticates using the currently logged-in Windows account.
• Basic, for sources that expect a username and password.
• Web API, which relies on a key or token.
• Organizational Account, for sources tied to a Microsoft 365 account.

Since the weather API in this example is fully open, Anonymous is the right choice. After a short pause while the query executes, the raw response lands in Power Query as JSON, which needs to be reshaped into a table before it's usable.

1. Select the list sitting inside the data column that was returned.
2. With that list selected, choose Convert to Table from the menu to switch it from a raw list into proper table structure.
3. Use the column expand button to choose which fields you actually want, temperature, conditions, wind speed, whatever the response includes, then confirm to apply the formatting.

Example Two: Connecting to an API That Requires a Bearer Token

Plenty of APIs won't hand over data without a valid bearer token attached to the request headers. There are two practical ways to get that token into Power BI, depending on how long the token stays valid.

Option One: Generate the Token Externally

If the API you're working with issues tokens with a long shelf life, some last for months, this approach is the simpler of the two. Generate a token using a tool like Postman, or however the provider's documentation recommends, and then bring it into Power BI manually.

1. Open Power BI Desktop, click Get Data, choose Web, and enter the API URL.
2. Click Advanced, place the URL in the URL Parts field, add an Authorization entry to the headers, format it as Bearer followed by your token, and confirm.

If the token is valid, the response comes through into Power Query just like the open API example. The catch is that this token will eventually expire, anywhere from a few minutes to many months depending on the provider, so a fresh one needs generating and re-entering whenever the old one stops working.

Option Two: Generate the Token Dynamically Inside Power BI

For APIs that issue short-lived tokens, minutes rather than months, manually regenerating a token every time isn't practical. Instead, you can write a small piece of M code that logs in and fetches a fresh token automatically every time the report refreshes.

As an example, picture an inventory management platform where authentication works by sending a username and password to a login endpoint, which then returns a bearer token in its response. The M code for that login step would look something like this:

let

url = "https://api.example-inventory.com/auth/login",

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

Running this query authenticates against the login endpoint and returns the token string. From there, turn that query into a reusable function:

1. In the Advanced Editor, add empty parentheses () right before the let keyword, then confirm. This converts the static query into a callable function.
2. Open the dataset query that needs the token and, in its Authorization header, replace the hardcoded token text with a call to that function instead, keeping a space between the word Bearer and the function call.

With that wired up, every time the report refreshes, Power BI calls the login function fresh, grabs a brand-new token, and uses it for that refresh's data pull, all without anyone needing to manually regenerate or paste in a new token.

Worth knowing: Storing a username and password directly inside M code works for getting something running quickly, but it's not a great long-term setup for anything shared beyond your own machine. Where the API supports it, an API key or a more limited-scope credential is a safer choice than your full account password.

Bringing It Together

Building a custom Power BI connector really comes down to three things working in sequence: confirming the target system actually exposes an API, understanding which of the four authentication methods that API expects, and writing M code that requests, authenticates, and reshapes the response into a usable table. Open APIs are the simplest starting point to practice on, and once bearer-token handling and dynamic token generation feel comfortable, most other authentication schemes turn out to be variations on the same underlying pattern.