Power BI Connector Extensibility - When to Build a Custom Connector and When Not To
Every Australian business seems to have at least one system that Power BI doesn't talk to out of the box. A practice management tool built by a two-person vendor in Adelaide. A job costing platform used across the construction industry that only exposes a REST API with a slightly odd paging scheme. A homegrown .NET app that someone wrote in 2014 and that still runs payroll adjustments.
When people want that data in Power BI, the first attempt is usually a Web.Contents call pasted into Power Query from a forum post. It works for a while. Then the token expires, or the API starts paginating, or someone else needs the same data and copies the query into their own report with a slightly different filter. Six months later there are eleven versions of the same API call scattered across the tenant, all with different bugs.
Connector extensibility is Microsoft's answer to that. It lets you package the logic for talking to a data source into a proper connector, which then shows up in the Get Data dialog like any built-in source. I like it a lot for the right situations. I also think it gets reached for too often, so let's talk about both.
How it works
Power BI connectors are written in the M language (the same language behind Power Query) and packaged as a .mez or .pqx file. A connector can define:
- The navigation table users see when they connect, so they pick "Projects" or "Timesheets" rather than building URLs
- Authentication, including OAuth flows, API keys, Windows auth or basic username and password
- Paging, retries and rate limit handling, written once and reused everywhere
- Query folding, if you want to push filters back to the source rather than pulling everything
You build connectors using the Power Query SDK, which now lives as an extension in Visual Studio Code. It gives you a project template, a way to run and test queries against your connector and tooling to build the final file.
On the Power BI Desktop side, custom connectors go in the [Documents]\Power BI Desktop\Custom Connectors folder. By default Desktop will refuse to load them, because they're uncertified code running on your machine. The Data Extensions security setting under Options controls this. You can either lower it to allow any extension to load, or keep the recommended setting and only allow connectors that are signed with a certificate your organisation trusts.
There are really three tiers:
- Certified connectors - built by a vendor or partner, reviewed by Microsoft, shipped inside Power BI. Users don't have to do anything special.
- Trusted third-party connectors - signed with a certificate, and the thumbprint registered in the machine's registry so Desktop loads them under the recommended security level.
- Uncertified custom connectors - unsigned, and only load if someone lowers the security setting.
The security question you should not skip
This is the part I'd want any IT manager to read carefully. A Power BI connector is code. It runs with the user's permissions and can call whatever endpoints it's written to call. Lowering the Data Extensions setting to "Allow any extension to load" means any .mez file dropped into that folder will execute.
For a single developer testing their own connector, that's fine. For a whole organisation, it is a bad idea. We have walked into tenants where the security setting was lowered on every analyst's laptop via Group Policy so one finance connector would work. That opens the door for every other connector someone downloads from GitHub.
The better approach is to sign your connector and distribute the thumbprint through the TrustedCertificateThumbprints registry key, managed centrally with Intune or Group Policy. It's more work up front. It also means your security team can tell exactly which connectors are allowed and nobody needs to lower a global setting. If your organisation is in a regulated industry, I'd treat this as the only acceptable option.
The gateway is where people get caught
Building a connector that works in Desktop is the easy half. The moment you publish a report and want scheduled refresh in the Power BI service, you need an on-premises data gateway with the connector installed. The service itself won't run custom connectors directly.
That means:
- Installing a gateway, if you don't already have one, and keeping it patched
- Copying the connector file into the gateway's custom connectors folder and enabling custom connectors in the gateway settings
- Making sure the connector's auth method is one the gateway supports for refresh. OAuth flows in particular need care, because the refresh token handling has to be right or refresh will fail a few hours after the credentials are entered
- Updating every gateway in the cluster every time the connector changes
We wrote about the gateway side in more detail in our post on Power BI gateways and custom connectors. The short version: if you don't have someone who owns the gateway, a custom connector will become an orphan very quickly.
When building one makes sense
From what we've seen, a custom connector earns its keep when most of these are true:
- Several people or reports need the same source. One analyst with one report doesn't need a connector. Ten reports across three teams do.
- The API is awkward. Cursor-based paging, OAuth with refresh tokens, rate limits that need backoff, nested JSON that needs reshaping. These are the things that people get subtly wrong when they copy queries around.
- The source isn't going away. If the business is migrating off the system next year, don't build a connector for it.
- You have someone who can maintain M code. This is the one people underestimate. When the vendor changes their API, someone has to fix the connector, rebuild it, re-sign it and redeploy it to every gateway and desktop.
We built one recently for a professional services firm whose practice management system had a reasonable API but OAuth with short-lived tokens and pagination via a next link buried in a response header. Their analysts had tried three times to do it in plain Power Query and each version broke in a different way. A connector with a navigation table of about eight entities, proper token refresh and a retry policy took a bit over a week including testing and gateway setup. It has been running for months with one small fix when the vendor added a field. That's a good outcome.
When it doesn't
There are a few situations where I'd steer people away:
You're on Fabric or heading there. If the goal is getting data into a lakehouse or warehouse anyway, it often makes more sense to land the API data with a Data Factory pipeline, a notebook or a small Azure Function, and point Power BI at the result. You get history, you decouple report refresh from the API's availability and you avoid the gateway entirely. For most of our newer clients this is the default answer now.
The vendor has a certified connector on the roadmap. Ask them. Some vendors have a certified connector in preview or an OData feed nobody mentioned in the sales process.
It's really a data engineering problem. If you need to join the API data with three other systems, handle late-arriving records and keep a full history, a Power Query connector is the wrong tool. Connectors are for getting data in cleanly, not for building a data platform.
There's nobody to own it. I'll repeat this one because it's the most common way custom connectors fail. A clever contractor builds it, leaves, and two years later the certificate expires and nobody knows how to re-sign the file.
Practical tips if you do build one
- Write the connector so it returns a navigation table. Users should never have to type a URL or know which endpoint to call.
- Handle paging and throttling inside the connector, with sensible retry and backoff. Use
Value.WaitForor similar patterns rather than hammering the API. - Implement query folding only if it really matters. It adds complexity, and for many SaaS APIs the filtering options are limited anyway.
- Add a test connection function so the gateway can validate the data source properly.
- Keep the connector source in Git, with a simple build script that produces the signed file. Future you will be grateful.
- Document the certificate expiry date somewhere people will see it, and put a reminder in a shared calendar.
Getting help
Connector extensibility is one of those Power BI features that's simple to start and easy to get wrong in production, mostly because of the security and gateway sides rather than the M code. Our Power BI consultants build and maintain custom connectors for clients, and often the more useful thing we do is talk people out of building one and into a better data pipeline. If the source system needs a more serious integration, especially one that feeds AI or automation as well as reporting, our AI integration services team looks at the whole picture. And when the real need is a proper data platform, our Microsoft Data Factory consultants can set up the ingestion so reports just read clean tables.
My honest view: build a connector when the same awkward source is needed by lots of people and you can commit to owning it. Otherwise, land the data somewhere sensible first and let Power BI do what it's good at.