How to Create a Power BI Connector

While Power BI has a massive library of built-in connectors, you'll eventually encounter a data source it doesn't support natively - like a niche SaaS tool, a proprietary internal API, or a legacy system. Don't worry, you aren't stuck exporting CSVs. This tutorial will guide you through the process of building your very own custom connector for Power BI, putting the power to connect to any data source directly into your hands.

Why Create a Custom Power BI Connector?

You might wonder if it's worth the effort. For many situations, it absolutely is. The standard "Get Data from Web" option is fine for simple, one-off API calls, but a custom connector offers far more power and elegance. Here's why you might build one:

Connecting to unsupported APIs: This is the most common reason. You can build a connector for any REST API, giving you direct access to the data you need without messy workarounds.
Streamlining complex authentication: Many APIs require authentication methods like OAuth2 or API keys. A custom connector can handle all the complex credential-passing logic behind the scenes, making it a simple login experience for end-users.
Simplifying the user experience: A well-designed connector presents a clean interface. Instead of forcing your team to paste API URLs into the generic web connector, you can provide a branded, straightforward-to-use option with simple input fields.
Pre-shaping the data: You can build logic directly into your connector to clean, transform, and shape data before it even lands in the Power Query editor. This enforces consistency and saves your report builders significant time.

What You'll Need to Get Started

Before beginning, you'll need a few tools and a bit of knowledge. Having these in place will make the process much smoother.

Power BI Desktop: The free application where you'll be testing your connector.
Visual Studio: You'll need the 2019 or a later version. Be sure to have the ".NET desktop development" workload installed.
Power Query SDK: This is a simple extension for Visual Studio that you can install from the Visual Studio Marketplace. It provides the project template you need.
Familiarity with M Language: The code behind Power Query. If you've spent time in the Advanced Editor, you've seen M. You don't need to be a top expert, but a basic understanding is necessary.
API Knowledge: You should understand the basics of how to make calls to a REST API and understand documentation for things like endpoints, headers, and response formats (usually JSON).
API Documentation: Have the documentation for the service you're connecting to handy. This is your map for what endpoints to call and how to authenticate.

Step-by-Step Guide to Building a Power BI Connector

Let's walk through the creation of a simple connector. For this example, we’ll use a free, public API that requires no authentication: the Cat Facts API. It’s a great starting point for understanding the fundamentals.

Step 1: Set Up Your Development Environment

First, get your tools ready.

Install Power BI Desktop and Visual Studio if you haven’t already.
Install the Power Query SDK. Open Visual Studio, go to Extensions > Manage Extensions, search online for "Power Query SDK", and install it. You may need to restart Visual Studio afterward.

Step 2: Create a New Data Connector Project

With the SDK installed, you can now create a project.

Open Visual Studio and select Create a new project.
Search for "Power Query" in the project templates search bar and select Data Connector Project. Click Next.
Name your project (e.g., "CatFactConnector") and choose a location to save it. Click Create.

Visual Studio will create a project containing a few key files:

CatFactConnector.pq: This is where you’ll write all your M code. It's the core of the connector.
resources.resx: A resource file for strings. This is useful for localization if you plan to support multiple languages.
PNG files: Several icon files. You can replace these with your own branding to make the connector look professional in Power BI's "Get Data" dialog.

Step 3: Write the Basic Connector Code

Now, open the CatFactConnector.pq file. You'll see some boilerplate code. Let's replace it with a simple program to call our Cat Facts API.

Here’s the complete code you will need. This code defines the connector and a function to fetch data from the API endpoint.

Let's break down what this code does:

section CatFactConnector: This simply declares a new section, which is a namespacing mechanism in M to keep your code organized. This needs to be done on the top of your .pq file.
[DataSource.Kind=..., Publish=...] and shared CatFactConnector.Contents = () =>: This defines our main exported function. The text within the brackets is metadata. DataSource.Kind tells Power BI this is a new type of data source. We name the function CatFactConnector.Contents. This code fetches the data.
let ... in ...: This shows a list of steps using standard M syntax.
source = Json.Document(Web.Contents("https://catfact.ninja/fact")): This performs two actions. First, Web.Contents() makes an HTTP GET request to the Cat Facts API. Second, Json.Document() parses the response from JSON text into a structured record.
fact and length: Pulls out the fact and length values from the JSON data.
table = #table({"Fact", "Length"}, {{fact, length}}): Turns that simple data into a table, which can be displayed directly in Power BI.
CatFactConnector = [ ... ]: Defines the data source with no authentication (Implicit = []) and a friendly label.
CatFactConnector.Publish = [ ... ]: Publishes the connector, providing a name and description, and categorizes it in Power BI.

Step 4: Test and Debug Your Connector

You can see your code as a work in progress without needing to compile anything. Simply click the "Start" button from the Visual Studio toolbar or press F5. This will launch a special instance of Power BI Desktop with debugging enabled, allowing you to test your connector without deploying it.

Inside Power BI, click on "Get Data" and locate your new 'Cat Fact Connector'. If you don’t see it immediately, use the search bar. Select it and click Connect. You should see a table with an interesting cat fact.

If problems occur, Visual Studio's debugging tools let you set breakpoints and step through code. The output window shows evaluation results, helpful for troubleshooting.

Final Thoughts

Creating a custom connector in Power BI unlocks data you couldn't normally access. You've set up your environment, written basic M code, and configured authentication. This skill is powerful for integrating complex or proprietary systems.

Custom connectors are highly useful for connecting to data sources like Shopify, Google Analytics, Salesforce, or Facebook Ads that may not have native connectors. If manual data entry or complex integrations are a challenge, consider using Graphed, which allows building connections and automating data workflows with a user-friendly interface.