Forum Discussion

ThomasLu_EDA's avatar
ThomasLu_EDA
New Member
1 year ago
Solved

Performing a query that combines a paginated response

I am trying to write a Power Query to pull data from Okta's Users API, which will be used to serve information related to an ongoing M&A. However, I'm encountering a lot of challenges trying to figure out the pagination.

 

Okta’s API uses a cursor-based pagination system as described in their documentation here: Okta Pagination Docs, so I'm trying to iterate through multiple API responses and combine all the data, or more specifically:

  1. Perform a request to Okta’s Users API endpoint to retrieve a list of users.
  2. Handle the cursor-based pagination to ensure I get all pages of results.
  3. Combine the paginated data into a single dataset for further analysis.

Using the below M/Power Query, I can get the first 200 records.

 

 

let
    apiUrl = "https://<company>.okta.com/api/v1/users",
    apiToken = "<api-token>",
    headers = [
        #"Authorization" = "SSWS " & apiToken
    ],

    // Make the API call to Okta
    Source = Json.Document(Web.Contents(apiUrl, [Headers=headers])),

    // Convert the JSON response to a table 
    users = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // Expand the records to access individual fields (id, status, created, profile, lastLogin)
    expandedUsers = Table.ExpandRecordColumn(users, "Column1", {"id", "status", "created", "profile", "lastLogin"}, {"ID", "Status", "Created", "Profile", "Last Login"}),

    // Expand the 'profile' field to get 'firstName', 'lastName', and 'login'
    expandedProfile = Table.ExpandRecordColumn(expandedUsers, "Profile", {"firstName", "lastName", "login"}, {"First Name", "Last Name", "Login"}),

    // Select only the columns we want
    finalTable = Table.SelectColumns(expandedProfile, {"ID", "First Name", "Last Name", "Login", "Status", "Created", "Last Login"})
in
    finalTable

 

 

 

When I try to refactor it to handle pagination as per below, I am still only able to get the initial 200 records via initialPage while allUsers and combineUsers is empty, when I should be getting ~350 records:

 

 

let
    // Define Okta API endpoint and API token
    apiUrl = "https://<company>.okta.com/api/v1/users",
    apiToken = "<api-token>",
    
    // Set the Authorization header
    headers = [
        #"Authorization" = "SSWS " & apiToken
    ],
    
    // Function to retrieve users from Okta API
    GetOktaUsers = (url as text) =>
    let
        // Make the API call to Okta
        response = Json.Document(Web.Contents(url, [Headers = headers])),
        
        // Extract the users (List of user records)
        users = response,
        
        // Get the next page URL from the response headers
        nextLink = try List.First(List.Select(response[links], each _[rel] = "next"))[href] otherwise null,
        
        // Return the users and the next page URL (if any)
        result = [Users = users, NextPage = nextLink]
    in
        result,

    // Initialize the process by calling the first page
    initialPage = GetOktaUsers(apiUrl),
    
    // Collect all users in a list, iterating over each page
    allUsers = List.Generate(
        ()=> [users = initialPage[Users], nextPage = initialPage[NextPage]],
        each List.Count([users]) > 0 and [nextPage] <> null,
        each [users = GetOktaUsers([nextPage])[Users], nextPage = GetOktaUsers([nextPage])[NextPage]],
        each [users]
    ),
    
    // Combine the results into a single list of users
    combinedUsers = List.Combine(allUsers),
    
    // Convert the combined list of users to a table
    usersTable = Table.FromList(combinedUsers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Expand the records to access individual fields (id, status, created, profile, lastLogin)
    expandedUsers = Table.ExpandRecordColumn(usersTable, "Column1", {"id", "status", "created", "profile", "lastLogin"}, {"ID", "Status", "Created", "Profile", "Last Login"}),
    
    // Expand the 'profile' field to get 'firstName', 'lastName', and 'login'
    expandedProfile = Table.ExpandRecordColumn(expandedUsers, "Profile", {"firstName", "lastName", "login"}, {"First Name", "Last Name", "Login"}),
    
    // Select only the columns we want
    finalTable = Table.SelectColumns(expandedProfile, {"ID", "First Name", "Last Name", "Login", "Status", "Created", "Last Login"})
in
    finalTable

 

 

 

Could anyone provide guidance or an example of how to achieve this in an PQ? Any help with handling the pagination loop and combining the data would be greatly appreciated!

 

A sample response with headers when querying the Okta User's API is:

 

 

[
  {
    "body": [
      {
        "id": "00abcdefghi123456789",
        "status": "STAGED",
        "activated": null,
        "statusChanged": null,
        "lastLogin": null,
        "passwordChanged": null,
        "profile": {
          "firstName": "Bruce",
          "lastName": "Wayne",
          "manager": "null null",
          "displayName": "Bruce Wayne",
          "organization": "Bat Family",
          "title": "Detective",
          "department": "Forensics",
          "login": "[email protected]",
          "email": "[email protected]"
        }
      },
      {
        "id": "00jklmnopq101112131",
        "status": "PROVISIONED",
        "activated": "2021-08-10T21:52:02.000Z",
        "statusChanged": "2024-10-09T07:54:19.000Z",
        "lastLogin": "2024-10-07T18:57:00.000Z",
        "passwordChanged": "2023-09-06T16:30:49.000Z",
        "profile": {
          "firstName": "Alfred",
          "lastName": "Pennyworth",
          "manager": "Bruce Wayne",
          "displayName": "Alfred Pennyworth",
          "organization": "Bat Family",
          "title": "Butler",
          "department": "Health",
          "login": "[email protected]",
          "email": "[email protected]"
        }
      }
    ],
    "headers": {
      "date": "Thu, 07 Nov 2024 04:50:02 GMT",
      "content-type": "application/json",
      "link": "<https://<company>.okta.com/api/v1/users?limit=200>; rel=\"self\", <https://<company>.okta.com/api/v1/users?after=200jklmnopq101112131&limit=200>; rel=\"next\"",
      "x-content-type-options": "nosniff",
      "strict-transport-security": "max-age=315360000; includeSubDomains"
    },
    "statusCode": 200,
    "statusMessage": "OK"
  }
]

 

 

in which case I am trying to call https://company.okta.com/api/v1/users?after=200jklmnopq101112131&limit=200 afterwards