z57r6 (z57r6) asked a question.
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 OKLink: <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, letiterations = 10, // Number of iterationsurl = "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 =>letSource = 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]inres, 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]) inGeneratedList
Hi Setu,
Okta's API returns an HTTP response header called "Link" which contains a URL for the next page. It doesn't seem to be currently possible for Power Query to look at the HTTP response headers (although it does support HTTP request headers, but that's a different story).
One quick workaround is to use a PowerShell script like:
https://github.com/mbegan/Okta-Scripts/blob/master/saveEventLogs.md
to export logs to a JSON file which can be consumed by Power Query.
An alternative would be to write a custom connector.
Hopefully Microsoft will add the ability to use HTTP response headers in a future release.
Gabriel Sroka
See update: https://github.com/gabrielsroka/gabrielsroka.github.io/blob/master/Okta.pq
python code
hope this helps