en / fr
Leveraging APIs in Power BI: JWT Authentication and Pagination with Power Query
📆 Published on
8 min read
mack by Macktireh

Leveraging APIs in Power BI: JWT Authentication and Pagination with Power Query

Table of Contents


Introduction

If you use Power BI, you’ve probably already needed to import data from a REST API. And you know what? It’s not always a walk in the park! Between authentication, HTTP headers, pagination… it’s easy to get lost.

In this article, I’ll show you concretely how to import data from an API into Power BI using Power Query. We’ll go through how to handle JWT authentication and pagination, all with a practical example based on the API DummyJSON . It’s a free and simple API that perfectly simulates real-world use cases.

Before getting started, I assume that you:

  • Already know the basics of Power BI Desktop
  • Have opened the Power Query editor at least once
  • Understand the basic concepts of REST APIs (GET, POST, headers, JSON)

If that’s the case, perfect! Let’s dive in.


Exploring the DummyJSON API

Before diving into the code, let’s take a few minutes to understand how the API we’re going to use works. DummyJSON is a great resource that provides fake data (products, users, posts, etc.) and simulates typical mechanisms of real APIs: authentication, pagination, etc.

Here’s the general process for retrieving data from a protected API:

Data retrieval steps diagram

Step 1: Authentication

Sending credentials

The first thing to do is authenticate. We send a POST request to the /auth/login endpoint with our credentials in JSON format:

{
  "username": "emilys",
  "password": "emilyspass"
}

Receiving tokens

In response, the API returns a JSON object with two important tokens:

  • accessToken: this is our key to access protected data
  • refreshToken: it will be used to renew the access token when it expires

Step 2: Data retrieval

Sending the request with the token

Now that we have our accessToken, we can request data! We send a GET request to /auth/products including the token in the Authorization header like this: Bearer your_access_token.

Receiving the data

The API returns a JSON object containing:

  • The list of products in the products field
  • Pagination info: total (total number of items), skip (how many items were skipped), limit (how many items per page)

Here’s a simplified response example:

{
  "products": [
    {
      "id": 1,
      "title": "iPhone 13 Pro",
      "description": "Latest iPhone model with advanced features",
      "category": "Electronics",
      "price": 999.99,
      "discountPercentage": 10.5,
      "rating": 4.7,
      "stock": 50,
      "brand": "Apple",
      ...
    },
    ...
  ],
  "total": 80,
  "skip": 0,
  "limit": 20
}

You get the idea? Now that we understand how it works, let’s move on to implementing it in Power Query!


Configuring Power Query Parameters and Functions

Let’s get into the code! We’ll build our solution step by step by creating reusable parameters and functions. The idea is to do things cleanly from the start to make maintenance and future evolution easier.

1. Configuring global Power Query parameters

Let’s start by creating parameters that centralize all our configuration. That way, if you need to change the API URL or your credentials, you’ll only have one place to update!

How to do it:

  1. In the Power Query Editor, go to the Home tab
  2. Click Manage Parameters > New Parameter
  3. Create the following parameters:

    • API_BASE_URL :

    • API_LOGIN_USERNAME :

      • Description : Your username
      • Type : Text
      • Value : emilys

    • API_LOGIN_PASSWORD :

      • Description : Your password
      • Type : Text
      • Value : emilyspass

    • API_PAGINATION_SKIP :

      • Description : Pagination starting point
      • Type : Decimal Number
      • Value : 0

    • API_PAGINATION_LIMIT :

      • Description : Number of items per page
      • Type : Decimal Number
      • Value : 20
Power Query parameters

2. Utility functions for HTTP requests

Now, we’ll create four “building block” functions that will make our lives easier for all API calls. These functions will handle headers, query parameters, and HTTP calls themselves.

How to create a function:

  1. In the Power Query Editor, Home tab
  2. Click New Source > Blank Query
  3. Open the advanced editor
  4. Paste the function code
  5. Give it a clear name

2.1 GetHeaderParameters: Prepare HTTP headers

This function configures the base headers required for our HTTP requests. It sets the content type to JSON by default and can accept additional headers if needed.

GetHeadersParameters
let
    GetHeadersParameters = (optional Headers as record) =>
        let
            Headers = Record.Combine({
                [#"Content-Type" = "application/json"],
                (Headers ?? [])
            })
        in
            Headers
in
    GetHeadersParameters

It creates a record with Content-Type: application/json and merges it with any additional headers passed as parameters.

2.2 GetQueryParameters: Handle pagination parameters

This function prepares URL parameters for pagination. It uses the global parameters we defined earlier.

GetQueryParameters
let
    GetQueryParameters = (optional Skip as number, optional Query as record) =>
        let
            Query = Record.Combine({
                [
                    skip = Number.ToText(Skip ?? API_PAGINATION_SKIP),
                    limit = Number.ToText(API_PAGINATION_LIMIT)
                ],
                (Query ?? [])
            })
        in
            Query
in
    GetQueryParameters

It builds an object with skip and limit parameters for pagination and can include additional parameters if provided.

2.3 HttpClientGetRequest: Perform GET requests

Here’s our function for GET requests. It uses Power Query’s Web.Contents to make the HTTP call.

HttpClientGetRequest
let
    HttpClientGetRequest = (
        RelativePath as text,
        optional Query as record,
        optional Headers as record
    ) =>
        let
            Options = [
                RelativePath = RelativePath,
                Query = (Query ?? []),
                Headers = (Headers ?? [])
            ],
            RawData = Web.Contents(API_BASE_URL, Options),
            Json = Json.Document(RawData)
        in
            Json
in
    HttpClientGetRequest

It combines the base URL with the relative path, adds query parameters and headers, performs the HTTP call, and returns the result as JSON.

2.4 HttpClientPostRequest: Perform POST requests

Same principle as GET, but for POST requests that include a request body.

HttpClientPostRequest
let
    HttpClientPostRequest = (
        RelativePath as text,
        optional Query as record,
        optional Headers as record,
        optional Body as record
    ) =>
        let
            Options = [
                RelativePath = RelativePath,
                Query = (Query ?? []),
                Headers = (Headers ?? []),
                Content = Json.FromValue(Body ?? [])
            ],
            RawData = Web.Contents(API_BASE_URL, Options),
            Json = Json.Document(RawData)
        in
            Json
in
    HttpClientPostRequest

Same as GET, but with an additional Body parameter. Adding Content automatically turns the request into POST.

Note

Why two separate functions?

You might wonder why I didn’t create a single generic function that handles both GET and POST. Good question! I tried it, and it works… but it introduces a problem.

Click to see the generic function and understand the issue
```powerquery title="HttpClient"
let
    HttpClient = (
        RelativePath as text,
        optional Query as record,
        optional Headers as record,
        optional Body as record
    ) =>
        let
            GetOptions = [
                RelativePath = RelativePath,
                Query = (Query ?? []),
                Headers = (Headers ?? [])
            ],
            PostOptions = [
                RelativePath = RelativePath,
                Query = (Query ?? []),
                Headers = (Headers ?? []),
                Content = Json.FromValue(Body ?? [])
            ],
            Options = if Body <> null and Record.FieldCount(Body) > 0
                then PostOptions
                else GetOptions,
            RawData = Web.Contents(API_BASE_URL, Options),
            Json = Json.Document(RawData)
        in
            Json
in
    HttpClient
```


**The problem:** Power Query considers `Web.Contents` options as dynamic (because of the `if`), which generates a warning in data source settings. Result? You won’t be able to schedule automatic refresh in Power BI Service!


![Data source settings](https://github.com/user-attachments/assets/2636a861-6fc0-4504-bf18-19656618322b)


That’s why I chose to split it into two separate functions. If you have a better solution, I’m all ears in the comments!

3. Authentication management

Now that we have our base functions, let’s handle JWT authentication.

3.1 GetJwtTokens: Retrieve access tokens

This function connects to the API and retrieves our precious tokens.

GetJwtTokens
let
    GetJwtTokens = () =>
        let
            Headers = GetHeadersParameters(),
            Query = [],
            Body = [
                username = API_LOGIN_USERNAME,
                password = API_LOGIN_PASSWORD
            ],
            Response = HttpClientPostRequest("auth/login", Query, Headers, Body),
            JwtTokens = [
                accessToken = Record.Field(Response, "accessToken"),
                refreshToken = Record.Field(Response, "refreshToken")
            ]
        in
            JwtTokens
in
    GetJwtTokens

It sends a POST request with your credentials to /auth/login and extracts access and refresh tokens from the response.

3.2 GetHeadersParametersWithAccessToken: Add token to headers

This function combines everything we’ve done so far: it retrieves the token and adds it to HTTP headers.

GetHeadersParametersWithAccessToken
let
    GetHeadersParametersWithAccessToken = (optional Headers as record) =>
        let
            JwtTokens = GetJwtTokens(),
            accessToken = Record.Field(JwtTokens, "accessToken"),
            Headers = Record.Combine({
                GetHeadersParameters((Headers ?? [])),
                [Authorization = Text.Format("Bearer #{0}", {accessToken})]
            })
        in
            Headers
in
    GetHeadersParametersWithAccessToken

It calls GetJwtTokens(), retrieves the access token, and adds it to an Authorization: Bearer token header. That way, all your authenticated requests are ready to go!


4. Data retrieval and pagination management

Now we reach the core of the system: the function that orchestrates everything to retrieve data page by page.

FetchDataFromApi
let
    FetchDataFromApi = (
        RelativePath as text,
        ResultsFieldName as text,
        TotalFieldName as text
    ) =>
        let
            // Internal function: retrieves total number of items
            RetrieveTotalCount = (FieldName as text) =>
                let
                    ApiResponse = HttpClientGetRequest(
                        RelativePath,
                        GetQueryParameters(),
                        GetHeadersParametersWithAccessToken()
                    ),
                    TotalCount = Record.Field(ApiResponse, FieldName)
                in
                    TotalCount,


            // Internal function: retrieves a specific page
            FetchPage = (FieldName as text, PageIndex as number) =>
                let
                    Offset = (PageIndex ?? 0) * API_PAGINATION_LIMIT,
                    QueryParams = GetQueryParameters(Offset),
                    AuthHeaders = GetHeadersParametersWithAccessToken(),
                    ApiResponse = HttpClientGetRequest(
                        RelativePath,
                        QueryParams,
                        AuthHeaders
                    ),
                    Results = Record.Field(ApiResponse, FieldName)
                in
                    Results,


            // Main function: retrieves all pages
            GetAllData = () =>
                let
                    TotalCount = RetrieveTotalCount(TotalFieldName),
                    MaxCount = List.Max({API_PAGINATION_LIMIT, TotalCount}),
                    TotalPages = Number.RoundUp(MaxCount / API_PAGINATION_LIMIT),
                    PageIndices = {0..TotalPages - 1},
                    AllPages = List.Transform(
                        PageIndices,
                        each FetchPage(ResultsFieldName, _)
                    ),
                    CombinedPages = List.Union(AllPages),
                    DataTable = Table.FromList(
                        CombinedPages,
                        Splitter.SplitByNothing(),
                        null,
                        null,
                        ExtraValues.Error
                    ),
                    ColumnIndices = List.Positions(Table.ColumnNames(DataTable)),
                    RecordColumnIndex = List.First(
                        List.Select(
                            ColumnIndices,
                            each Type.Is(
                                Value.Type(Record.FieldValues(DataTable{0}){_}),
                                type record
                            )
                        )
                    ),
                    RecordColumnName = Table.ColumnNames(DataTable){RecordColumnIndex},
                    RecordFieldNames = Record.FieldNames(
                        Record.FieldValues(DataTable{0}){RecordColumnIndex}
                    ),
                    ExpandedTable = Table.ExpandRecordColumn(
                        DataTable,
                        RecordColumnName,
                        RecordFieldNames,
                        RecordFieldNames
                    )
                in
                    ExpandedTable
        in
            GetAllData()
in
    FetchDataFromApi

I won’t go into every line of code detail, I encourage you to review it to fully understand how it works. But here’s what this function does:

  1. It needs three inputs:

    • RelativePath: API path (example: “auth/products”)
    • ResultsFieldName: name of the results field (example: “products”)
    • TotalFieldName: name of the total field (example: “total”)

  2. It first counts: makes an initial call just to know how many total items there are

  3. It fetches page by page: calculates how many pages to download, then retrieves each page one by one

  4. It merges everything: combines all pages into a single list

  5. It cleans and structures: transforms the list into a clean Power Query table with properly expanded columns

Basically, you give it the API path and field names, and it returns a full table with all the data. Pretty neat, right?


5. Organizing your functions

Quick tip: create folders in Power Query to organize your parameters and functions. It really helps keep your project clean!

Here’s how I usually structure my projects:

Project file structure

Importing data into Power BI (Using the FetchDataFromApi function)

Now that everything is set up, let’s see how simple it is to use!

Retrieve products

Create a new blank query and paste this code:

Products
let
    Source = FetchDataFromApi("auth/products", "products", "total")
in
    Source

That’s it! Really, that’s it.

PowerQuery code for products query

Click Done and watch your data load:

Products query

Retrieve users

Same principle, just as simple:

Users
let
    Source = FetchDataFromApi("auth/users", "users", "total")
in
    Source

See? Once the groundwork is done, adding new data sources becomes super easy!

Tip

Visualize your dependencies

Power Query offers a very useful view called “Query Dependencies” that graphically shows how all your functions and queries are connected. It’s perfect for:

  • Understanding your project structure at a glance
  • Verifying everything is properly connected
  • Spotting potential dependency issues

To access it: View tab > Query Dependencies

Query Dependencies

Conclusion

That’s a wrap!

In this article, we covered how to:

  • Properly structure a Power Query project with reusable parameters and functions
  • Handle JWT authentication with an API
  • Automatically retrieve all pages of data using pagination
  • Transform everything into clean, ready-to-use tables in Power BI

The big advantage of this approach is that you now have a reusable system. Need to connect to a new API? Just adapt the parameters and paths—the rest is already done!

A few tips to go further:

  • Adapt the code to your own APIs (they all behave slightly differently)
  • Add error handling if needed
  • Document your functions with comments
  • Always test

If you liked this reusable function-based approach, you’ll love these two related articles that follow the same philosophy:

🔗 Flexible data source management Do you manage dashboards that need to run across different environments? Discover how to dynamically adapt your MySQL connections depending on the environment (development, testing, production). It’s the perfect complement if you work with large databases and want to avoid reconfiguring sources manually every time.

📁 Cleanly importing multiple Excel/CSV files Tired of seeing your Power Query editor cluttered with dozens of queries when importing multiple files? I show you how to create a custom function to elegantly combine Excel or CSV files .