Graphed LogoGraphed
Login

How Does Power BI Connect to REST API?

Cody Schneider•

Connecting Power BI to a REST API is the key to unlocking live data from countless web services and applications, feeding it directly into your dashboards. It's the difference between looking at last week's CSV export and seeing what's happening in your business right now. This guide will walk you through the entire process, from making the initial connection and handling authentication to tackling common challenges like pagination so you can pull all your data, not just the first page.

What is a REST API and Why Connect it to Power BI?

Think of a REST API as a standardized way for different software applications to talk to each other over the internet. It’s like a waiter in a restaurant. You (your Power BI report) give the waiter (the API) your order (a request for specific data), and the waiter brings that order back from the kitchen (the web service or app, like HubSpot, Shopify, or a project management tool).

Why bother with this instead of just downloading a CSV file? Three main reasons:

  • Automation: Your reports update automatically. Once you set up the connection, you can schedule refreshes in Power BI Service, and it will pull fresh data without you lifting a finger. No more manually downloading files every Monday morning.

  • Real-Time Data: You get access to the most current information available. This is essential for monitoring live marketing campaigns, tracking sales performance, or checking inventory levels.

  • Comprehensiveness: APIs often provide access to more data fields and deeper insights than what's available in standard user interface exports. You can pull the exact data you need, in the structure you want.

Getting Started: What You'll Need

Before you jump into Power BI, make sure you have a few things ready. A little preparation goes a long way and will save you from hitting roadblocks later.

  • Power BI Desktop: This is where you'll build the connection and the report. If you don't have it, you can download it for free from Microsoft.

  • The API Endpoint URL: This is the specific web address you're requesting data from. A simple one might look like https://api.example.com/v1/sales.

  • API Documentation: This is your most important tool. The documentation tells you everything you need to know: the correct URLs, how to authenticate, what data is available, and if there are any request limits. Never skip reading the docs!

  • Authentication Credentials: If the API isn't public, you'll need a way to prove you have permission to access the data. This is usually an API Key, an OAuth token, or a username and password. The documentation will tell you which one to use.

Step-by-Step: How to Connect Power BI to a REST API

Let's walk through the fundamental process of pulling data from an API source. We'll use a hypothetical sales data API for our examples.

Step 1: Get Data From Web

Open a new Power BI report. In the Home tab of the ribbon, click on Get Data and select Web. If you don't see it right away, you might find it under the "Other" category.

Step 2: Enter the API URL

A dialog box will appear asking for a URL. Start with the "Basic" option and enter your API endpoint URL. For now, let’s use a simple endpoint that doesn't require complex authentication or parameters:

https://api.yourcompany.com/v1/orders

Click OK. If you're trying to access a complicated URL with lots of parameters, click on "Advanced" to build the query using URL parts, which can be easier to manage.

Step 3: Handle API Authentication

Power BI will now ask you how you want to authenticate. This is where that API documentation comes in handy.

  • Anonymous: For public APIs that don't require any login.

  • Basic: For APIs that require a simple username and password.

  • Web API: This is the one you'll use most often. It's for APIs that require a key sent in the HTTP header.

Most modern REST APIs use a bearer token or API key for authentication. To set this up, select Web API. Power BI will ask for a key, but what you usually need to do is specify an HTTP request header where the key goes.

Look at your API documentation for the authorization header format. It will likely be something like Authorization for the header name and Bearer YOUR_API_KEY or ApiKey YOUR_API_KEY for the value. Replicate this in the header field Power BI provides.

Authorization: Bearer abc123xyz456

Once you are authenticated correctly, click Connect.

Step 4: Transform JSON Data in Power Query

You’ll now be in the Power Query Editor, and your data will likely look like a single record or list, usually because API data is returned in JSON format. Don't worry, this is normal. Now it's time to transform it into a usable table.

  1. Convert to Table: If you see a list, click the To Table button in the "Transform" tab. A confirmation pop-up will appear, just click OK.

  2. Expand the Columns: You should now have a column full of "Record" links. Click the expand icon (two arrows pointing in opposite directions) in the column header.

  3. Select Your Columns: Power Query will show you a list of all the fields inside the records. Uncheck "Use original column name as prefix" (this keeps your column names clean) and select the fields you want in your report.

Just like that, you have converted nested JSON from an API into a structured table ready for your Power BI dashboard! You can now rename columns, change data types, and perform any other transformations before clicking Close & Apply.

Handling Common API Challenges

Connecting to a basic endpoint is straightforward, but real-world scenarios often involve more complexity. Here's how to handle two of the most common challenges: pagination and dynamic parameters.

Challenge 1: Pagination (Getting All the Data)

Most APIs don't return all their data in a single request. Imagine you have 50,000 orders, sending that much data at once would be slow and inefficient. Instead, APIs use "pagination," giving you data in pages - say, 100 orders at a time.

Your job is to tell Power BI how to go to the next page, and the next, until it's collected everything. This usually requires a bit of M code magic in the Advanced Editor.

A Strategy for Paging Through API Results:

  1. Identify the Paging Method: Check the API docs. Does it use an "offset" parameter (&offset=100)? A "page" number parameter (&page=2)? Or does the API response include a URL for the next page of results? The latter is most common and easiest to work with.

  2. Create a Function: The best approach is to create a function in Power Query that takes a URL as input, fetches the data from it, and finds the "next page" URL within the response. A simplified version looks like this:

    • Duplicate your original query.

    • Go to Advanced Editor and wrap the code in a function declaration that accepts a URL as a parameter, which might begin with (url as text) =>.

    • Modify the function to parse the JSON and return not only the data but also the URL for the next page. Often this comes in a field named next, nextLink, or similar.

  3. Iterate the Function: You can then use the List.Generate() function to repeatedly call your custom function, starting with the first page URL and stopping when the "next page" URL is null or empty.

Here’s a simplified M code snippet to illustrate the concept:

let Source = List.Generate( // Initializer: a record with the first page of results () => [Result = Json.Document(Web.Contents("https://api.yourcompany.com/v1/orders?limit=100")), Page = 1], // Condition: keep going as long as the Result is not empty each not List.IsEmpty([Result][data]), // Next function: get the data from the 'next_page' URL provided in the response each [ Result = Json.Document(Web.Contents([Result][paging][next])), Page = [Page] + 1 ] ) in Source

This is an advanced technique, but it's essential for pulling large datasets. Start with samples online and adapt them to your API's specific structure.

Challenge 2: Using Dynamic Parameters

What if you want to pull data only for a specific date range, or for a particular product category? Hardcoding these values in the URL isn't flexible. Instead, you can use Power Query parameters.

  1. In the Power Query Editor, go to the Home tab and click Manage Parameters > New Parameter.

  2. Create parameters for StartDate and EndDate. Set their type to "Date." Set a current value for each.

  3. Go back to your main data query and click the gear icon next to the "Source" step. This will open the initial Web connector dialogue.

  4. Switch to the Advanced view. Here, you can build your URL dynamically. Use your parameters in the URL query string: &start={StartDate}&end={EndDate}. Power BI will replace the {ParameterName} part with the current parameter value.

Now, users of your report can simply change the parameter values to update the data they see without ever touching the M code.

Final Thoughts

Connecting Power BI to a REST API transforms your reporting from static snapshots to a dynamic, automated system fueled by live data. By using the Web connector, understanding how to navigate JSON, and learning to handle authentication and pagination, you can tap into almost any web-based data source you need.

While mastering Power Query offers immense flexibility, we know that building and troubleshooting these API connections, especially across multiple marketing and sales platforms, can quickly consume hours you don't have. We built Graphed to remove this friction entirely. Instead of tweaking M code, you just connect your platforms like Google Analytics, Shopify, or Salesforce in a few clicks and tell Graphed what you want to see in simple language to get a real-time dashboard instantly, freeing you up to act on your data instead of just wrangling it.