How to Call API in Power BI

Getting data directly from an application's API into Power BI is a total game-changer. It frees you from the endless cycle of downloading CSV files, cleaning them up, and manually uploading them every time you need a fresh report. This article will walk you through exactly how to connect Power BI to a web API, handle authentication, and transform that data into a usable format for your dashboards.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Connect Power BI to an API?

If you're used to the "export CSV" routine, you might wonder why you should bother with an API. The answer comes down to three key benefits: automation, accuracy, and access to more data.

• Automation: Once you set up an API connection, you can schedule data refreshes. Your reports and dashboards will update automatically (daily, hourly, etc.) without you having to lift a finger. This saves hours of manual work, especially for weekly or monthly reporting.
• Accuracy: Manual data exports are prone to human error. You might download the wrong date range, forget a column, or make a mistake while cleaning the data in Excel. An API connection pulls the data directly from the source system, ensuring you're always working with the ground truth.
• Richer Data: Applications often provide far more data through their API than they do in their standard CSV exports. Connecting directly can give you access to granular details and new metrics you couldn't report on before, opening up deeper analysis opportunities.

Before You Start: What You'll Need

To make this process as smooth as possible, gather a few things before you open Power BI. This will save you from having to hunt for information mid-process.

1. Power BI Desktop: This guide assumes you are using the free Power BI Desktop application, which is where you build your reports.
2. The API URL (Endpoint): This is the specific web address you're going to get the data from. For example, it might look something like https://api.example.com/v1/sales-data.
3. API Documentation: This is a developer's best friend and your most crucial resource. The documentation will tell you the exact URL endpoints, what data is available, and, most importantly, how to authenticate. Don't skip this step!
4. Authentication Credentials: Most APIs require some form of authentication to prove you have permission to access the data. This could be a unique API key, a username/password, or a more complex OAuth token. Check the documentation to see what your specific API requires.

How to Connect to an API in Power BI: A Step-by-Step Guide

With your information gathered, you're ready to pull in some live data. Let's walk through the exact steps inside Power BI Desktop.

Step 1: Open Power BI and Use 'Get Data'

First, fire up Power BI Desktop. In the "Home" tab on the top ribbon, you'll see a prominent Get Data button. Click on it. This will open a window showing all the different data sources Power BI can connect to.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Select the 'Web' Connector

In the "Get Data" window, there are hundreds of options. You can type "Web" into the search bar or find it under the "Other" category. Select Web and click Connect.

Step 3: Enter the API URL

Next, you'll see a dialog box asking for a URL. This is where you paste the API endpoint you found earlier.

You have two options here: Basic and Advanced.

• For simple 'GET' requests where you just need to pull data from a single URL, Basic is all you need.
• The Advanced option is useful if you need to add things like HTTP request headers, which are often required for authentication (more on that in a moment).

For now, paste your URL into the 'Basic' field and click OK.

https://api.your-saas-tool.com/v1/data-endpoint

Step 4: Configure Authentication

This is often the trickiest part of the process. After you enter the URL, Power BI will ask how you want to authenticate. The option you choose here must match what the API documentation specifies.

• Anonymous: Use this option for public APIs that don't require any login or key. This is rare for business applications but common for public data sets (weather, city data, etc.).
• Windows / Basic: Choose 'Basic' if the API requires a simple username and password for authentication.
• Web API: This option is designed for APIs that use an API Key. You'll enter the key provided by the service here. The documentation should tell you if the key needs to be passed in a header or as a URL parameter. If it must be a header, you should've used the 'Advanced' URL option in the previous step to add it, like Authorization: Bearer YOUR_API_KEY.
• Organizational account: This is for connecting to Microsoft services within your organization.

Select the appropriate method, enter your credentials, and click Connect.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 5: Transform the Data in Power Query Editor

If your credentials are correct, Power BI will successfully call the API. What happens next is that the Power Query Editor will open. This is where you'll shape the raw API output into a clean, usable table.

APIs almost always return data in a format called JSON (JavaScript Object Notation), which looks a bit like nested lists and records. Power Query is exceptionally good at parsing this.

Your screen will likely show a single 'Record' or 'List'. Here's how to turn it into a proper table:

1. Convert to Table: Look for a button in the top left or in the "Transform" tab called To Table. Click it. If your data is a list of records, this will instantly turn it into a table with a single column containing 'Records'.
2. Expand the Column: Click on the little icon with two opposing arrows at the top of your new column. This is the 'Expand' button. Power BI will show you all the fields (or 'keys') inside the records. Uncheck "Use original column name as prefix" to keep your column names clean, then click OK.

Boom! Your nested JSON data is now a flat table with proper columns representing each data field. From here, you can perform other transformations like changing data types (e.g., text to number), renaming columns, or filtering rows, just as you would with any other data source.

Step 6: Close & Apply

Once you are happy with the shape of your data in the Power Query Editor, click the Close & Apply button in the top-left corner. Power BI will load the data into your data model. You can now use the fields from the API to build visualizations just like you would with an Excel spreadsheet.

Advanced Considerations & Common Hurdles

Real-world APIs aren't always so simple. Here are a couple of advanced topics you might run into.

Handling Pagination

What happens if your request only returns the first 100 results, but there are thousands available? This is called pagination. The API delivers data in "pages," and the response will usually include a URL or a parameter to get the next page (e.g., https://api.example.com/data?page=2).

Dealing with this requires more advanced Power Query techniques, often involving creating a custom function that loops through the pages until all the data is collected. This topic is advanced enough for its own article, but it's important to know the concept exists and to check your API's documentation for "pagination" or "limits."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

API Rate Limits

Most APIs limit how many times you can request data in a given period (e.g., 100 requests per minute). When setting up your scheduled refresh in the Power BI service, be mindful of these limits. Triggering too many refreshes too quickly could get your access temporarily blocked. A daily refresh is usually safe for most business dashboards.

Final Thoughts

Connecting Power BI directly to an API is one of the most powerful skills you can learn to automate your reporting. By using the 'Web' connector and the straightforward tools in the Power Query Editor, you can create dashboards that are always up-to-date, accurate, and require zero manual intervention after the initial setup has been completed.

While mastering Power Query is a valuable proficiency, sometimes you want answers without having to read API documentation and navigate JSON structures. That’s exactly why we built Graphed. We provide pre-built, one-click connections to popular marketing and sales platforms, handling all the complex API work behind the scenes. Instead of transforming data manually, you can simply ask for what you need in plain English, and our system will build the dashboard for you in seconds.