Can Power BI Use API as Data Source?

So, you want to pull data directly from an application programming interface (API) into Power BI. The short answer is yes, you absolutely can, and it's a game-changer for creating automated, real-time dashboards. This article will walk you through exactly how to use Power BI's built-in web connector to fetch data from an API, transform it into a usable format, and start building reports without ever downloading a CSV again.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Connect Power BI to an API Directly?

If you're used to the classic routine of exporting spreadsheets from various marketing and sales platforms, you already know the pain. It's manual, time-consuming, and by the time you've built your report, the data is already old. Connecting directly to an API solves this by creating a live, automated pipeline between your data source and your Power BI dashboard.

Here are the biggest benefits:

• Access to Real-Time Data: Your dashboards can be refreshed on a schedule, pulling the most up-to-date information directly from the source. This is perfect for monitoring live campaign performance, sales metrics, or website traffic.
• Full Automation: You eliminate the tedious, error-prone task of manual data exports. Once the connection is set up, your report updates itself. This frees up hours of your week that were once spent on data wrangling.
• Vast Data Universe: The web is full of public and private APIs for everything from government statistics and weather forecasts to social media analytics and stock prices. You can tap into these sources to enrich your existing business data and uncover new insights.
• A Single Source of Truth: By pulling data directly from sources like Shopify, Google Analytics, or your CRM, you ensure everyone on your team is looking at the exact same numbers, right from the platform itself.

Understanding API Basics Before You Start

Before jumping into Power BI, let's quickly cover a few basic API concepts. You don't need to be a developer to understand this, but knowing these terms will make the process much smoother.

What’s an API Endpoint?

Think of an API endpoint as a specific URL that provides data. Just like visiting a website URL shows you a webpage, "visiting" an API endpoint URL with a tool like Power BI returns a set of raw data. For example, a company might have one endpoint to get customer data and another to get sales data.

https://api.examplemetrics.com/v1/users

Data Format: Usually JSON

Most modern APIs return data in a format called JSON (JavaScript Object Notation). It looks a bit technical at first, but it’s just a structured way of organizing data using key-value pairs. Power BI's data transformation tool, Power Query, is excellent at parsing JSON and turning it into a clean table.

A simple snippet of JSON might look like this:

{ "id": 1, "name": "Jane Doe", "email": "jane@example.com", "plan": "Pro" }

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Authentication

Many APIs, especially those from SaaS tools that hold your private data, require authentication to make sure you have permission to access the data. This usually involves an API key - a long, unique string of text you include in your request. It works a bit like a password, telling the API who you are and that you’re authorized to pull information.

Rate Limits

APIs often have rate limits, which are rules about how many times you can request data in a certain period (e.g., 100 requests per minute). This prevents any single user from overwhelming their servers. It's something to be aware of when setting up your automatic refresh schedules in Power BI.

Step-by-Step: Connecting Power BI to a Public API

Alright, let's build something. The best way to learn is by doing. We'll connect Power BI to a free, public API called JSONPlaceholder. It’s a handy tool for testing that provides sample data without needing an API key. We'll use the /todos endpoint, which serves up a sample to-do list.

The endpoint URL is: https://jsonplaceholder.typicode.com/todos

Step 1: Open Power BI and Select 'Web' Source

Fire up Power BI Desktop. In the Home ribbon, click on Get Data. This opens up an enormous list of data sources. You can either scroll to find Web or simply type "Web" into the search bar. Select it and click Connect.

Step 2: Enter the API Endpoint URL

A dialog box will appear asking for the URL. Make sure the Basic option is selected and paste our sample API endpoint into the text field:

https://jsonplaceholder.typicode.com/todos

Click OK. Power BI will now connect to that URL and retrieve the data.

Step 3: Navigating the Power Query Editor

Because you've connected to a JSON API, Power BI is smart enough to open the Power Query Editor. Power Query is where you clean, transform, and shape your data before it's loaded into your Power BI report.

At first, you won't see a nice table. Instead, you'll likely see a list of Records. This is because the API returned a list of JSON objects (our "todos"), and each line item represents one object.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 4: Convert the List to a Table

This is a quick but essential step. In the Power Query Editor, you'll see a Transform tab at the very top. Click it, and on the far left, you'll find a button that says To Table. Click this button.

A small dialog will pop up. The default settings are fine, so just click OK. Your list of records has now successfully been converted into a single-column table. We're almost there!

Step 5: Expand the Column to See Your Data

Your table now has one column (probably named "Column1") that still contains the Records. Notice the special icon in the column header - two arrows pointing in opposite directions. This is the Expand button, and it's the magic trick for working with JSON data in Power Query.

Click the Expand icon. A dropdown menu will appear showing you all the key-value pairs from the JSON objects: userId, id, title, and completed. By default, all are selected.

💡 Pro Tip: Uncheck the box that says "Use original column name as prefix." If you leave it checked, your new columns will be named things like "Column1.userId," which isn't very clean.

Click OK.

And just like that, Power Query has transformed the nested JSON data into a clean, flat table with proper columns and rows. You'll see separate columns for userId, id, title, and completed.

Step 6: Clean Up Data Types and Load

Power Query is usually good at guessing data types, but it's always best to double-check. Glance at the icon in each column header. Make sure numbers are formatted as numbers (e.g., "1.2"), text as text ("ABC"), and True/False values as boolean ("TRUE/FALSE").

Once everything looks good, go to the Home tab in Power Query and click the Close & Apply button. This will close the editor and load your shiny new table into your Power BI data model, ready for you to create visuals and reports.

Handling Common API Challenges in Power BI

The example above is straightforward, but real-world APIs often have a couple more hurdles. Here's how to handle them.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

APIs That Require Authentication

What if your API requires an API key? You can add this in the same "Web" source dialog box. When you first enter the URL, instead of clicking OK, select the Advanced option.

This reveals a section for HTTP request header parameters. This is where you pass your authentication key. Your API's documentation will tell you the exact format, but a common one uses an "Authorization" header:

• In the first text box, type: Authorization
• In the second, you might type: Bearer YOUR_SECRET_API_KEY (replace with your actual key)

This securely includes your API key with the request, granting you access to the data.

Handling Pagination

If an API has thousands of records to return, it won't send them all at once. Instead, it "paginates" the data, a process through which you get data in chunks, or pages (e.g., 100 records at a time). Your initial request gets Page 1, and the response usually includes a URL to request Page 2, and so on.

Handling this in Power BI is a more advanced topic that requires creating a custom function in Power BI’s M language to loop through all the pages and combine the results. While it's beyond the scope of this intro guide, it's absolutely possible and is the key to pulling large datasets from robust APIs.

Final Thoughts

Connecting Power BI to an API is a powerful skill that can drastically upgrade your reporting process. By leveraging the built-in "Web" connector and the Power Query Editor, you can automate data pipelines from countless sources, giving you access to real-time information to make smarter, faster decisions.

While connecting directly in Power BI unlocks a lot of potential, we know navigating Power Query, and especially tackling tricky APIs with authentication or pagination can become a major time sink. That’s why we built Graphed to remove this friction entirely. Instead of configuring headers and manually cleaning JSON, you connect your business platforms with one-click integrations. From there, you just ask questions in plain English - like "Show me our campaign ROI from Facebook Ads this month" - and we generate a live, interactive dashboard instantly. The goal is to get you straight to the insights, without the headache of data wrangling.