How to Get Data from API to Power BI

Tired of manually exporting CSVs and uploading them into Power BI just to get your reports updated? There is a much better way. By connecting Power BI directly to an Application Programming Interface (API), you can pull live, real-time data straight from your favorite tools into your dashboards. This guide will show you exactly how to connect Power BI to an API, from simple, public data feeds to more complex sources that require authentication and handle multiple pages of data.

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

Think of an API as a structured messenger between different software applications. When you use an app on your phone, it’s using APIs to fetch weather data, post photos, or get sports scores. For a data analyst or marketer, an API is a direct pipeline to the raw data sitting inside popular SaaS platforms like HubSpot, Shopify, Mailchimp, or your own company's internal systems.

Instead of relying on the pre-built reporting inside an application, connecting it to its API with Power BI gives you complete control. Here’s why that’s so powerful:

Automated, Real-Time Data: Once connected, you can schedule refreshes in Power BI. Your dashboards will update automatically (daily, hourly, or even more frequently) without you ever having to download another file.
Access to All Your Data: Standard exports often give you a limited, pre-packaged view of your data. APIs typically provide access to a much wider and more granular range of fields and metrics, allowing you to build the exact report you need.
Unifying Multiple Sources: You can pull data from the Google Ads API, the Facebook Marketing API, and your Salesforce API all into a single Power BI report. This allows you to create comprehensive dashboards that tell the full story of your business performance.

Getting Started: What You'll Need

Before you jump in, you’ll need a few things ready. Don't worry, they're all straightforward.

Power BI Desktop: This is the free version of Power BI available from Microsoft's website. You'll need it installed to build your reports before you publish them.
An API URL (Endpoint): This is the specific web address you're going to get the data from. The API documentation for whatever service you're using will provide you with a list of available endpoints. For example, an endpoint for customer data might look like https://api.yourcrm.com/v1/customers.
Authentication Details (Usually): Most business APIs require some form of authentication to protect data and track usage. This is most often an API Key or an OAuth token, which is just a long string of text you include in your request to prove you have permission to access the data.

Step-by-Step Guide: How to Connect to a Simple API

Let's start with the most basic connection: a public API that doesn't require any authentication. We’ll use a free "dummy" API called JSONPlaceholder, which is perfect for practicing.

Our goal: Get a list of blog posts from the https://jsonplaceholder.typicode.com/posts endpoint.

1. Open Power BI and Select the Web Connector

In Power BI Desktop, go to the Home tab on the ribbon and click Get data. In the dropdown list, choose Web.

2. Enter the API URL

A small window will pop up asking for the URL. Select the Basic option and paste your API endpoint. In our case, that's https://jsonplaceholder.typicode.com/posts. Then click OK.

Power BI will now connect to that URL and a credentials window may appear. Since this is a public API, you can just click Connect using the default "Anonymous" access.

3. Transform the Data in Power Query

You'll now see the Power Query Editor. This is where you transform the data before loading it into your report. The initial output from a JSON-based API often looks like a "List of Records". This isn't very useful yet, so we need to convert it into a proper table.

Convert to Table: In the top-left, click the Into Table button. A small dialog will appear, you can just click OK with the default settings.
Expand the Columns: You should now see a single column named "Column1" containing a series of "Record" links. At the top of this column's header, you’ll see an expand icon (two arrows pointing in opposite directions). Click it.
Select Fields: A dialog will pop up showing all the available fields inside the record (e.g., userId, id, title, body). Make sure they are all checked and uncheck the box that says "Use original column name as prefix." Click OK.

Voilà! Your data is now properly formatted in a clean table with columns for userId, id, title, and body. You can rename the columns or change data types as needed. When you’re ready, click Close & Apply in the top-left to load it into your Power BI report.

How to Connect to an API That Requires an API Key

Most business APIs aren't public - you need to prove you have access. Let's look at how to use an API Key, the most common form of authentication.

Where you put the key depends entirely on what the API documentation specifies. Usually, it goes into the HTTP Headers of your request. A common header name is Authorization, X-Api-Key, or something similar.

1. Get Data from Web (Advanced)

Again, go to Get data > Web. This time, click the Advanced radio button.

2. Configure the Advanced Request

The advanced dialog gives you more control. Here's how you use it:

In the URL parts section, paste the main API endpoint (e.g., https://api.weather.com/v1/forecast).
Down below, in the HTTP request header parameters (optional) section, you'll add the API key. Let’s imagine the documentation tells us to use a header called X-Api-Key.
In the first input box, type X-Api-Key. In the second box right next to it, paste your actual API key (it will be a long string of random characters like ab123cde456fgh789).

If the documentation says to use an "Authorization" header with a "Bearer Token," you'd type Authorization in the first box and Bearer your_api_key_goes_here in the second. Pay close attention to the format, as it needs to be exact.

Once you click OK, Power BI will make the request with your API key, and if successful, you'll be brought to the Power Query Editor to transform your data just like before.

Handling APIs with Multiple Pages (Pagination)

What happens when you have thousands of records? To keep requests fast and manageable, most APIs will only return data in chunks, or "pages" - maybe 100 records at a time. This is called pagination. Your query will need to loop through all the pages to get the complete dataset.

While this is a more advanced topic, it's a common problem. Power Query's M language can solve this by creating a custom function. Here is a simplified approach you can adapt.

Step 1: Inspect the API Response

First, make a simple request to your endpoint and see how it tells you to get the next page. Often, the response will include a URL for the next page of results, sometimes with a key named next or links.next. You'll need this URL pattern to build your loop.

Step 2: Create a Function to Get Data from Each Page

We'll create a reusable function in Power Query that can fetch a page of data and tell us where the next page is.

In the Power Query home ribbon, go to New Source > Blank Query.
Right-click the new query in the left pane, rename it to something descriptive like fn_GetAllPages, and open the Advanced Editor.
Delete the default text and paste in the following M code. This template is a starting point - you may need to adjust it based on your API’s specific structure.

Remember to replace YOUR_API_KEY and adjust [results] and [next] to match the actual names used by your API. Once you're done, click Done.

Step 3: Run the Function and Combine Data

Now, you can use that function to grab all your data.

Create another Blank Query and open the Advanced Editor.
Paste in this code, which will repeatedly call the function you just made until there are no more pages left to fetch.

Click Done. This query will now loop through every page of your API endpoint and combine all the results into a single table, ready for you to expand and use in your reports.

Final Thoughts

Connecting Power BI to an API unlocks a powerful way to automate your reports with live, detailed data from the services you use every day. By using the Web connector, you can move away from manual exports and build robust, automated dashboards that give you a real-time pulse on your business.

While connecting directly is powerful, dealing with authentication, reading API documentation, and managing pagination can still feel like a technical chore. This is exactly why we built Graphed. We provide one-click integrations for platforms like Google Analytics, Shopify, Facebook Ads, and Salesforce, handling all the complex API connections behind the scenes for you. Instead of writing code or setting up headers, you just connect your account and ask in plain English for the dashboard you want ("Show me my ad spend versus revenue this month"), and we build it for you with real-time data - no technical expertise required.