How to Use REST API in Power BI

Bringing data directly from your favorite SaaS application into a Power BI dashboard can feel like a game-changer. The quickest path is often through something called a REST API, a powerful way to make different software talk to each other. This tutorial will walk you through exactly how to connect to a REST API in Power BI, without needing a degree in computer science.

Graphed

Still Building Reports Manually?

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

Watch the Demo

So, What's a REST API Anyway? A Simple Analogy

Talking about APIs can get technical fast, so let's use an analogy. Imagine you're at a restaurant.

• You, the diner (or in this case, Power BI), want to get some food (data).
• The kitchen (a server or application like HubSpot or Shopify) holds all the food (data).
• You can't just walk into the kitchen. You need a waiter (the API) to take your order.

A REST API is just a type of waiter that follows a specific set of rules (the "REST" part) to make sure everyone understands each other. You give the waiter a clear order (a "request"), and they bring you your food in a predictable format (a "response," usually a data language called JSON).

Key Terms You'll Encounter

• Endpoint (the menu item): This is the specific URL you use to request a certain piece of data. One endpoint might get you customer data, while another gets you sales data.
• GET Request (your order): This is the most common type of request. You’re simply asking to "get" data from the server.
• JSON (your food on the plate): The format the data is usually returned in. It looks a bit like code but is highly structured, making it easy for applications like Power BI to understand.
• Authentication (your ID): Many APIs require you to prove you have permission to access the data. This is often done with an "API Key" - a unique password you include with your request.

Your Pre-Flight Checklist: Before Opening Power BI

Getting organized before you dive into Power BI will save you a lot of time. You'll need three key things from the application you want to pull data from.

1. Find the API Documentation

The API documentation is the restaurant’s menu. It's the most important resource you have. It tells you everything you need to know: what endpoints are available, what parameters they accept, and how to authenticate. A quick Google search for "[SaaS Application Name] API Docs" is usually all you need. For example, "Shopify API Docs."

Graphed

Still Building Reports Manually?

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

Watch the Demo

2. Get the Specific Endpoint URL

Once you’re in the documentation, look for the data you want to access. Find the specific "GET" endpoint URL for that data. For this tutorial, we’ll use a simple, free, and open-source JSON placeholder API that doesn't require a key. This lets us focus on the Power BI steps without worrying about authentication first.

Our example endpoint URL will be: https://jsonplaceholder.typicode.com/posts

This URL will give us a list of sample blog posts - perfect for learning the ropes.

3. Understand the Authentication Method

If you're connecting to your own business data (like from Salesforce or Facebook Ads), the documentation will have a section on "Authentication." Read it carefully. Most services use one of a few common methods:

• API Key: A single, long string of text you send with your request. This is very common for its simplicity.
• OAuth: A more secure but more complex process where Power BI receives permission to access data on your behalf without you sharing passwords. This is the "Sign in with Google" style of authentication.
• Basic Auth: Simply using a username and password.

Step-by-Step Guide: Connecting a REST API to Power BI

With our checklist complete, it's time to pull this data into Power BI. We’ll use the simple placeholder API so you can follow along easily.

Step 1: Get Data from Web

First, open a new Power BI Desktop file. In the Home tab of the ribbon, click on Get data and then select Web from the list of options.

Step 2: Enter the API Endpoint URL

A dialog box will appear. Select the Basic option and paste your API Endpoint URL into the field. For our example, this is:

https://jsonplaceholder.typicode.com/posts

Click OK. Power BI will now connect to that URL and fetch the response. Since this is an open API, you won't be prompted for credentials.

Note: If your API required a key in the header, you would click "Advanced," where you can add HTTP request headers. You’d enter the header name required by the API docs (like "Authorization" or "X-Api-Key") and then your key in the value field.

Step 3: Dive into the Power Query Editor

After Power BI connects, the Power Query Editor window will open automatically. This is where you transform the raw API data into a usable table. What you'll see might look a bit strange at first, it will be a list of "Records."

This is the raw JSON response. Power BI sees there's a list, but it hasn't unpacked the data inside each list item yet. Our next step is to turn this list into a structured table.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Convert the List to a Table

In the Power Query Editor's top ribbon, you'll see a tab called Transform. Don’t click that. Instead, look for a contextual tab called List Tools - Transform. In this tab, there’s a simple but vital button: To Table.

Click it. A small pop-up will ask about delimiters. Since we are creating a table from a list of records, you can just click OK with the default settings.

Step 5: Expand the Columns to Reveal Your Data

You now have a single column (likely named "Column1") with the word "Record" repeated in each cell. This is progress! This means you have a table where each row contains a single record from the API.

Now, we need to extract the data fields from within each record.

Look at the header for "Column1." To the right of the name, you'll see a two-way arrow icon. This is the Expand button.

Click this icon. A dropdown will appear with a list of all the fields available in the records (e.g., userId, id, title, and body). By default, all are checked. For now, leave "Use original column name as prefix" unchecked, and click OK.

Voila! Your single column expands into four discrete columns, and you have clean, structured data ready for your report. You just successfully pulled data from a REST API into Power BI.

Step 6: Clean Up and Close & Apply

From here, you can use the Power Query Editor as you normally would. You might want to:

• Check that Power BI guessed the right data types (e.g., id is a number, title is text).
• Rename columns to be more user-friendly.
• Remove any columns you don’t need for your report.

Once you are happy with how your data looks, click the Close & Apply button in the top-left corner of the Home tab. The Power Query Editor will close, and your data model will be loaded, ready for you to start building visuals.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Handling a Common API Challenge: Pagination

You may follow all these steps and find you're missing data. Many APIs don't return all their data in one go, instead, they "paginate" it, giving you a "page" of results at a time (e.g., 100 records per request). The API documentation will tell you how to ask for the next page of results, often by adding a parameter to your URL, like ?page=2 or ?offset=100.

Handling this in Power BI is a more advanced step that often requires writing some M code in the Advanced Editor to create a custom function that loops through the pages until it has retrieved all the data.

While that is beyond the scope of this beginner's guide, just knowing pagination exists is half the battle. If your initial results seem incomplete, a missing page parameter is the most likely culprit.

Final Thoughts

Connecting Power BI directly to a REST API might seem technical, but it’s an incredibly valuable skill that opens up access to countless data sources. By understanding the core concepts and following these steps, you can pull live data from almost any application right into your dashboards, ready for analysis.

While this manual process in Power BI is powerful, digging through API documentation, handling authentication, and writing scripts to manage pagination can become time-consuming. At Graphed , we’ve built a platform that automates these tricky connections for the most popular marketing and sales platforms. You just connect your apps securely and then use natural language to ask for the dashboards you need - we handle all the API calls, authentication, and data structuring behind the scenes, so you get to the insights in seconds, not hours.