Curious about what a Power Query custom data connector can do for you—or for your customers?
Maybe you offer a product or service that exposes data. Would a custom connector help users more easily access your app’s data? Perhaps it’s not your own external offering, but rather a tool from someone else that your teams uses internally. You’re wondering if a custom connector could reduce the pain currently involved when interfacing with it. Or, maybe you’re just curious: You’d like to understand what a custom connector can offer, so that, down the road, you’ll be better positioned to evaluate whether one of these connectors would be an appropriate component to bring into the picture.
So what exactly does a custom connector offer you?
The ability to make interacting with your data source more efficient, convenient and faster to set up from Power Query in Microsoft Power BI.
At the technical level, a custom connector offers you these possibilities by giving you the ability to customize or control three aspects of a Power Query user’s interactions with your data source:
- Connection Configuration & Authentication
- Navigation (how a user finds the data/objects of interest)
- Data Access (such as providing an easy-to-use interface for what otherwise be a complex sequence of API calls)
Connection Configuration & Authentication
Out of the three areas, this may be the least exciting, but that doesn’t mean it is unimportant. A custom connector has the ability to handle certain authentication scenarios that are not, or are not easily, implemented in M code that users can write and run directly in Query Editor. In particular, a custom connector can handle non-Azure AD OAuth authentication. It also supports API key authentication that is usable from the Power BI Service (something not supported from regular M code that’s published to the Service).
[More: Handling authentication & Additional connector functionality: Authentication]
Navigation
Typically, after a user connects to a data connector, a navigation menu (a.k.a. navigation table) is displayed. Listed out are the objects (tables, views, functions, etc.) which the user can access using the connector.
At first glance, navigation may seem unexciting. The ability to list out the tables in a database may seem so basic it hardly merits mention. Turns out, your connector can do creative things when it renders out its navigation table, which opens up possibilities.
For an example of a creative navigation table, take the Dataverse connector. Behind the scenes, a Dataverse environment’s data is exposed as a SQL Server database, so it’s valid to skip using this connector and instead use connector Sql.Database, manually specifying the name of the server to connect to.
However, with the Dataverse connector, the process is simplified. Based on your credentials, the connector fetches a list of the environments you have access to. It uses this to present you with a hierarchical navigation table where you first pick the environment of interest, then are presented with a list of objects (tables, etc.) in that environment. The connector’s creative navigation table saves you from having to know each environment’s database server name or, for that matter, even which environments you have access to. You simply connect using your credentials, choose the environment of interest, then choose from the objects you have access to—no manual entry of server names required!
In this screenshot, notice how “Ben Gribaudo’s Environment” shows up nested under “Dataverse.” I didn’t need to know the SQL Server hostname for this environment’s database (or even that its data is accessed via SQL). Dataverse’s navigation table simplified my experience, allowing me to focus on choosing from the data I have access to, not on finding server names.
In contrast, sometimes it’s not the exciting that’s important, but the practical. Take the case of a web API. Unless it supports OData, Power Query isn’t able to auto-discover the tables and methods the API exposes. Instead, your users will be left consulting the API’s reference docs to find endpoint URLs for each desired piece of functionality then hand-writing M code calls using Web.Contents…unless, that is, you have a custom connector for your API with a navigation table that lists out what your service offers. A “little” nicety like this can go a long way toward making your API easy to use.
[More: Navigation table icon possibilities]
Data Access
Accessing data on a Power Query-supported database platform tends to be straightforward, but this ease of use often doesn’t hold true when fetching data from an API.
It’s common for APIs to page data. Fetching a full table’s worth of data may require making a sequence of HTTP GET calls, with each call after the first passing back the “next token” (or “paging token”) received in response to the preceding call. In conjunction with this, the various result pages which are returned need to be stitched together into a single M table. For an an advanced Power Query developer, crafting the requisite M code that makes then transforms a series of API calls into a single, consolidated table of data is a complexity that is doable, but takes time. For a basic user, encountering this task may be show-stopping.
With a custom connector, these mechanics can be done once (i.e. by you) then hidden inside the connector. Connector users simply see a table—a table which the connector quietly takes care of stitching together on demand by making the necessary sequence of API calls. You wrote the complex code once; everyone gets to benefit from it.
This isn’t all that’s possible with a custom connector in the data access realm. Query folding is one of Power Query’s key features. Adding query folding support to a custom connector can pay significant dividends to your users (by giving them faster refreshes) and also potentially to you (by reducing load on your servers).
Conclusion
Primarily, a custom connector is about improving user experience—making it quick, convenient and efficient for a user to access data provided by a source.
Practically, you’re making an upfront investment by having one person (or team) craft the requisite, potentially complex M code needed to orchestrate interacting with your service. In turn, this saves each of your users from needing to invest the time and have the necessary skills to take on crafting their own integration code. Not only would this be a duplicated expenditure of effort many times over, but the prospects of needing to do this on their own may discourage some users to the point where they switch to a competitor’s offering that is easier to work with from Microsoft Power BI (e.g. because it provides a custom connector).
(Aside: If you need help with building a custom connector, please let me know….)