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:
- Perform a request to Okta’s Users API endpoint to retrieve a list of users.
- Handle the cursor-based pagination to ensure I get all pages of results.
- 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": "bruce.wayne@company.com",
"email": "bruce.wayne@company.com"
}
},
{
"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": "alfred.pennyworth@company.com",
"email": "alfred.pennyworth@company.com"
}
}
],
"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
