Using Custom Connectors in Power BI - When the Built-In Ones Are Not Enough

Power BI ships with a long list of built-in connectors. SQL Server, SharePoint, Dataverse, dozens of SaaS products, generic web and OData endpoints. For most reporting jobs one of those covers you. Then every so often a client has a system with no connector, an internal API, an obscure line-of-business platform, a data source with authentication that none of the standard options handle, and the neat built-in list suddenly has a hole in exactly the shape of the thing they need to report on.

That is where custom connectors come in, and Microsoft's documentation on using custom connectors with the on-premises data gateway covers the mechanics of getting one working in the service. I want to talk about the decision around them, because a custom connector is a real piece of software with a real maintenance cost, and the interesting question is usually not how to build one but whether you should. We have gone both ways on client projects, and the wrong call in either direction is expensive.

What a custom connector actually is

A custom connector is a piece of code, written in Power Query's M language and packaged as a .mez file, that teaches Power BI how to talk to a data source it does not natively understand. It handles the connection, the authentication, and the shaping of whatever the source returns into tables Power Query can work with. Once installed it shows up in the Get Data dialog alongside the built-in connectors, and to the report author it feels like any other source. That is the whole point. The complexity of talking to a strange API gets hidden inside the connector, and everyone downstream just picks it from a list.

The most common reasons to reach for one are a genuinely unsupported source with no off-the-shelf connector, an internal or industry-specific API that only your organisation uses, or a source whose authentication the standard web connector cannot handle cleanly. If your need is one of those, a custom connector can be the right tool. If it is not, there is very often a simpler path, and I will come back to that.

The gateway is where it gets fiddly

Building a connector that works in Power BI Desktop is the straightforward part. You drop the .mez file in the right folder, flip a setting to allow uncertified connectors, and it loads. The complication, and the reason Microsoft has a dedicated page for it, is getting that connector to work in the Power BI service on a scheduled refresh, which for most real data sources means going through the on-premises data gateway.

Here is the shape of it. When your report refreshes in the cloud, the service does not run your custom connector itself. It hands the job to the gateway, which is a piece of software running on a machine inside your network, and the gateway needs its own copy of your custom connector to do the work. So the connector has to be deployed in two places, the authors' Desktop installs and the gateway machine, and the gateway has to be configured to allow custom connectors from the folder you put it in. Miss that step and you get the classic failure where the report refreshes perfectly on someone's laptop and then errors the moment it runs on schedule in the service, because the gateway has never heard of the connector the report depends on.

This gateway-side deployment is the part teams underestimate. The connector is not a one-off install. Every time you update it you have to redeploy it to the gateway, and if you run a cluster of gateways for high availability, to every machine in the cluster. It is a small operational routine, but it is one, and it needs to belong to somebody. Sorting out this deployment and refresh path is a routine part of the data platform work our Power BI consultants do, because a connector that only runs on the author's machine is not a solution, it is a prototype.

Certified versus uncertified, and why it matters

Custom connectors come in two flavours in practice. Uncertified ones, which most in-house connectors are, and certified ones that Microsoft has reviewed and distributes. For an internal connector you build for your own organisation, you are almost always in uncertified territory, which means you have to explicitly allow uncertified connectors both in Desktop and on the gateway. That is a security setting, and it should be a deliberate decision, because you are telling Power BI to run code that Microsoft has not vetted.

That security angle is worth pausing on. A custom connector is code running inside your data path, often with credentials to a source system, executing on a gateway machine inside your network. Treat it like the piece of production software it is. Know who wrote it, where the source lives, and who can change it. I have seen custom connectors that nobody could locate the source code for, which is a genuinely bad position to be in when the source API changes and the connector breaks. If you build one, the M source belongs in version control like any other code, not in a .mez file on one person's machine.

Be honest about whether you need one at all

Now the part I care about most, because it is where the money is. Most of the time I see a team reaching for a custom connector, there is a simpler answer that they have not considered, and the custom connector is a solution looking for a problem.

If the source has a decent REST API, the built-in web connector plus some Power Query M can often talk to it directly, no custom connector required. It is less tidy, the query lives in the report rather than in a reusable component, but for a single report against a single API it is far less work and far less to maintain. If the awkward source is something you control, sometimes the right move is to have that system push its data into a place Power BI reads easily, a database or a Fabric lakehouse, rather than building a connector to pull from it. And if several reports need the same tricky source, the question becomes whether a connector is really the reusable component you want, or whether the data should be centralised once into a proper semantic model that every report reads from.

That last option is usually the better answer at any scale. A custom connector reused across ten reports means the same odd API gets hit ten different ways and maintained in one fragile component. Landing that data once into a governed model, and pointing the reports at the model, is sturdier and easier to reason about. Working out whether the answer is a connector, a bit of Power Query, or a rethink of where the data should live is exactly the kind of architecture call we work through in our business intelligence and Microsoft Fabric engagements, because the connector is often the most expensive way to solve a problem that has a cheaper solution.

The maintenance reality

If you do build a custom connector, go in clear-eyed about what you are signing up for. It is software, and software rots. The API it talks to will change. The authentication method will be deprecated. Power BI itself will update. Each of those can break your connector, and when it breaks, every report depending on it breaks with it. Someone has to own that connector, keep its source in version control, test it when the upstream API changes, and redeploy it to the gateways when it updates.

For a connector to a stable internal system that a lot of reporting genuinely depends on, that maintenance cost is justified and the connector earns its keep. For a one-off report against an API that some Power Query would have handled, it is overhead you created for yourself and will be quietly cursing in a year when it breaks and the author has moved on. The deciding factor is reuse and longevity. A connector that serves many reports for years is worth building and maintaining properly. A connector that serves one report is usually a sign you reached for the heavy tool too early.

The short version

Custom connectors let Power BI talk to sources it does not natively support, and for genuinely unsupported systems, internal APIs, or awkward authentication, they can be the right tool. The mechanics of building one are the easy part. The real work is the on-premises gateway, where the connector has to be deployed and redeployed to every gateway machine and explicitly allowed, or your scheduled refresh breaks in the service while working fine on the desktop. Treat a custom connector as the production code it is, keep its source in version control, and give it an owner. And before you build one at all, check whether a bit of Power Query, a data push, or a centralised semantic model solves the problem with far less to maintain, because most of the time it does.

If you are staring at a data source Power BI cannot see and trying to work out whether a custom connector is the answer or an expensive detour, that is a good conversation to have before you build. Take a look at our services or get in touch and we will help you find the least painful path to the data.