How to Connect API to Power BI

Connecting live data from an application directly into your dashboard can feel like a game-changer, and Power BI's ability to connect to APIs makes this possible. Instead of manually exporting and importing files, you can create reports that refresh automatically. This guide will walk you through the entire process, from understanding API basics to handling authentication and shaping the data for your dashboards.

What is an API and Why Use it with Power BI?

Think of an API (Application Programming Interface) as a menu at a restaurant. You don't go into the kitchen to cook your meal, you look at the menu (the API) and tell the waiter (the request) what you want, and they bring your food (the data) back to you. The kitchen (the other application's database) does all the work in the background.

In data analysis, an API is a structured way for one software application (like your e-commerce platform) to share data with another (like Power BI). Connecting Power BI to an API has several major advantages:

• Real-Time Data: Your reports can reflect the most up-to-date information available. When you refresh your Power BI dashboard, it sends a new request to the API and pulls in the latest data, eliminating the need for periodic manual exports.
• Automation: Say goodbye to the Monday morning ritual of downloading CSVs. Once you set up the API connection, the data pipeline is automated. You can schedule refreshes and trust that your reports are always current.
• Access to Wider Data Sources: Thousands of services offer APIs, giving you access to data you couldn't otherwise get easily. You can pull in sales data from Shopify, marketing performance from HubSpot, financial data from payment processors, or even public data like weather and stock prices.

Before You Start: What You'll Need

Before jumping into Power BI, a little prep work is necessary. The key to any successful API connection is understanding its rules, which are all laid out in the service's API documentation.

1. Find the API Documentation

This is your instruction manual. A quick search for "[Service Name] API Documentation" will usually find it. This document tells you everything you need to know, including the base URL for requests, what data you can get (endpoints), and how to authenticate.

2. Understand Endpoints and Parameters

An API doesn't just give you all its data at once. It organizes it into specific URLs called endpoints.

• A Base URL is the starting point for all requests, like https://api.example.com/v1/
• An Endpoint is a specific path that gets you a certain type of data, like /orders or /customers.

Combining them gives you your request URL: https://api.example.com/v1/orders.

You can often refine your request using parameters. These are added to the end of the URL after a ? to filter or specify what you want. For example:

https://api.example.com/v1/orders?status=shipped&limit=100

This URL asks for the 100 most recent orders that have a "shipped" status.

3. Check the Authentication Method

You can't just access a private service's data without permission. APIs use authentication to verify your identity. The documentation will tell you which method to use. Common types include:

• API Key: A unique string of text you include with your request to identify you. This is very common for its simplicity.
• Basic Authentication: Requires a simple username and password.
• OAuth 2.0: A more secure, token-based process often used by larger platforms like Google, Salesforce, and Facebook. It’s more complex and sometimes requires custom connector development beyond Power BI's basic web connector.

Connecting a Simple API to Power BI (Step-by-Step)

Let's walk through connecting a basic API that doesn’t require authentication. We'll use a sample JSON endpoint for this example: https://jsonplaceholder.typicode.com/posts. This is a free-to-use API for testing that returns sample blog post data.

Step 1: Get Data from Web

Open Power BI Desktop and on the Home ribbon, click Get data and select Web from the list.

Step 2: Enter the API URL

In the "From Web" dialog box, you'll see "Basic" and "Advanced" options. For a simple URL, the Basic option is fine.

Paste the endpoint URL into the text box: https://jsonplaceholder.typicode.com/posts

Step 3: Configure Authentication

After you click OK, Power BI will ask how you want to authenticate. Because this is a public API, authentication isn't needed.

Select Anonymous and click Connect.

Step 4: Navigate the Data in the Power Query Editor

Power BI will now open the Power Query Editor. Because the API returned data in JSON format, Power Query won't immediately display it as a clean table. Instead, you'll likely see a "List" of "Records."

This is where the magic happens. You need to convert this into a table.

1. On the Transform tab, click the To Table button. A new dialog box will appear. You can just click OK with the default settings.
2. You now have a table with a single column named "Column1" full of "Record" links. Click the expand arrows icon next to the "Column1" header.
3. Power Query will show you all the available fields from the records (like userId, id, title, and body). Make sure "Use original column name as prefix" is unchecked, and click OK.

And there you have it! Your API data is now in a clean, tabular format. You can now fix the data types for each column (e.g., ensure id is a Whole Number) and then click Close & Apply on the Home ribbon to load it into your Power BI model.

How to Handle APIs with Authentication

Most business APIs require authentication. Let's cover the most common type: the API key.

Connecting with an API Key

APIs typically expect the key to be sent in one of two ways: as a URL query parameter or as an HTTP header. The header method is generally more secure and preferred.

Here's how to connect using a header:

1. In Power BI, click Get data > Web again.
2. This time, select the Advanced option.
3. In "URL parts," enter the base API URL (e.g., https://api.yourapp.com/v1/data).
4. Under "HTTP request header parameters," add the header name the API documentation specifies. Common header names are Authorization, x-api-key, or Api-Key.
5. In the value field next to the header name, enter your API key. For Authorization headers, you may need to prefix it with "Bearer " (i.e., Bearer your-api-key-goes-here). The docs will tell you.
6. Click OK. Power BI will handle authentication behind the scenes, and you can proceed to transform the JSON data as shown in the previous section.

If the API asks for the key as a query parameter, you can just add it to the URL in the basic connector, like so:

https://api.yourapp.com/v1/data?api_key=your-key-goes-here

Advanced Topic: Handling Pagination

APIs will rarely send you all their data in a single request. If you have 100,000 orders, sending them at once would crash the server. Instead, they "paginate" the results, giving you data in chunks, or "pages." Your API call might return the first 100 orders and a link or parameter telling you how to get the next 100.

Handling this in Power BI requires moving beyond the basic UI and getting into the Advanced Editor to write M code, Power Query's language. While a full tutorial on M is out of scope here, the general approach is to create a custom function that does the following:

1. Makes an initial request to the first page of the API endpoint.
2. Parses the response to extract the data for that page.
3. Checks the response to see if there is a "next page" URL or marker.
4. If a "next page" exists, the function calls itself using that new URL.
5. It continues this loop until there are no more pages, combining the data from every page along the way.

This is an advanced technique, but it's essential for pulling comprehensive datasets from most APIs. If you find yourself needing to do this, searching for "Power BI API pagination M function" will provide many templates and examples to adapt.

Troubleshooting Common Errors

Connecting to APIs can be finicky. Here are a few common errors and how to solve them.

• "Access to the resource is forbidden" or "Unable to connect: The credentials provided were invalid." This is almost always an authentication problem. Double-check your API key or credentials. Make sure you placed the key in the correct place (header vs. URL parameter) and used the correct header name.
• "We couldn't parse the JSON or XML input." This means the API did not return data in a format Power BI understands. Check your full request URL in a web browser - if you get an error there, you've likely made a typo in the endpoint or a parameter.
• Scheduled Refresh Fails in Power BI Service. This often happens when dynamically building parts of your URL in Power Query. Power BI requires a static URL to configure a Data Source for scheduled refresh. To solve this, break down your URL into parameters within the Query Editor instead of concatenating text strings.

Final Thoughts

Connecting Power BI directly to APIs automates your reporting and unlocks access to timely data from systems across your business. While the process requires a bit of upfront configuration in the Power Query Editor, the payoff in time saved and insight gained is enormous.

We know this process can be complicated, especially when you have data scattered across a dozen different marketing and sales platforms. That's why we created Graphed. We handle all the API connections, data warehousing, and transformations for platforms like Google Analytics, Shopify, Salesforce, and Facebook Ads behind the scenes. Instead of wrestling with headers and pagination, you just connect your accounts and ask for the dashboard you need in plain English - our AI builds it for you in seconds with live, updating data.