pull out data from multiple web pages (pagination) in power query. Skip to main content
https://support.okta.com/help/answers?id=9062a000000quq2qac&refurl=http%3a%2f%2fsupport.okta.com%2fhelp%2fanswers
How satisfied are you with the Okta Help Center?
Thank you for your feedback!
How satisfied are you with the Okta Help Center?
1
2
3
4
5
Very Dissatisfied
Very satisfied
Enter content less than 200 characters.
Ask Search:
Setu PandyaSetu Pandya 

pull out data from multiple web pages (pagination) in power query.

Hi,
 
I am trying to pull out data from multiple web pages of okta api.
 
According to okta api documentation,
 
HTTP/1.1 200 OK
Link: <https://{yourOktaDomain}.com/api/v1/users?after=00ubfjQEMYBLRUWIEDKK (https://%7byourOktaDomain%7d.com/api/v1/users?after=00ubfjQEMYBLRUWIEDKK)>; rel="next",
  <https://{yourOktaDomain}.com/api/v1/users?after=00ub4tTFYKXCCZJSGFKM (https://%7byourOktaDomain%7d.com/api/v1/users?after=00ub4tTFYKXCCZJSGFKM)>; rel="self"
I need to add “rel=next” . I want to know how it can be done in power query.
 
I tried to pass 'rel=next' as option parameter 'Content'. I am getting 0 record.
 
I don't know if my logic is correct or not for cursor based pagination.
 
Here is my query,
 
let
iterations = 10, // Number of iterations
url = "https://xxxx.okta.com/api/v1/logs",
auth_key = "SSWS xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
header=[Authorization=auth_key , ContentType="application/json"],
content1 = "{
""rel"":""next"",
}",


FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url, [Headers=header, Content=Text.ToBinary(content1)])),
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
res = [Data=data, Next=next]
in
res,

GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data])



in
GeneratedList
Emilian AldeaEmilian Aldea (Okta, Inc.)
Hello Pandya,
 Emilian here with Okta's Customer Support Team, thanks for reaching out to us.
 Regarding the matter you've addressed, from the Okta API docs at https://developer.okta.com/docs/api/resources/users#list-users

"If you don’t specify a value for limit and don’t specify a query, only 200 results are returned for most orgs."

 One way you may address this is by adding pagination. I have attached below a support article that explains how you may set that up:
URL: https://developer.okta.com/docs/api/getting_started/design_principles.html#pagination

Hope this helps clear things out!
Best Regards,

Emilian Aldea
Technical Support Engineer
Okta Global Customer Care
Setu PandyaSetu Pandya
I want to know how it can be done in power query.
Michael de NijsMichael de Nijs
I'm struggling with the same question. The user API maximum results are 200 and with pagination you can get to the next page but how can we configure that within Powerquery and/or Power BI. The URL to pagination basically says nothing.
Victor Lysell AdminVictor Lysell Admin
I'm struggling with the same. 
Trevor GibbonsTrevor Gibbons
I am also struggling with the same, the documentation provides no direction on how to use the operators
Yuvraj Kukar (Admin)Yuvraj Kukar (Admin)
Anyone found the solution for it then please share. I am struggling to find it as well.