how to add "rel=next" in power query(pagination)?

[srpandya95]

I am trying to pull out data from okta api.

I want data from multiple web pages. Now, I am getting only 100 records.

Source = Json.Document(Web.Contents("https://xxxxxx.okta.com/api/v1/logs", [Headers=[Authorization="SSWS xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" , ContentType="application/json" , rel="next"]])),

According to Okta api document, I need to add "rel=next". How can I do that?

Design Principles | Okta Developer

Design Principles | Okta Developer

Pagination

Requests that return a list of resources may support paging. Pagination is based on a cursor and not on page number. The cursor is opaque to the client and specified in either the before or after query parameter. For some resources, you can also set a custom page size with the limit parameter.

Note that for technical reasons not all APIs respect pagination or the before and limit parameters, see the Events API for example.

Param Description

before	This is the cursor that points to the start of the page of data that has been returned.
after	This is the cursor that points to the end of the page of data that has been returned.
limit	This is the number of individual objects that are returned in each page.

Link Header

Pagination links are included in the Link header of responses. It is important to follow these Link header values instead of constructing your own URLs as query parameters or cursor formats may change without notice.

HTTP/1.1 200 OK
Link: <https://{yourOktaDomain}.com/api/v1/users?after=00ubfjQEMYBLRUWIEDKK>; rel="next",
  <https://{yourOktaDomain}.com/api/v1/users?after=00ub4tTFYKXCCZJSGFKM>; rel="self"

[gabrielsroka]

Hi @srpandya95

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.

[gabrielsroka]

Hi @srpandya95

See my update on

https://community.powerbi.com/t5/Integrations-with-Files-and/API-calls-with-Pagination/m-p/576872/hi...

[v-shex-msft]

Hi @srpandya95,

Based on test, rel not the default header parameter, I don't think you can direct use it as header parameter.

Please refer to below links to send 'rel=next' text as option parameter 'Query' or 'Content' and try again.

Connect to a Web Service sending parameters

Connecting to data source hosted on Dropbox

Regards,

Xiaoxin Sheng

[srpandya95]

Hi,

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

[v-shex-msft]

Hi @srpandya95,

@MarcelBeug Any suggestion for srpandya95's situation? I feel some confused on his queries.

Regards,

Xiaoxin Sheng

[MarcelBeug]

Unfortunatley not. I'm not familiair with Okta Api's and the explanation provided isn't very helpful to me.

[srpandya95]

Hi,

I am trying to pull out data from okta api. 

According to Okta api document, I need to add "rel=next". How can I form the query?

Source = Json.Document(Web.Contents("https://xxxxx.okta.com", [Headers=[Authorization="SSWS xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" , ContentType="application/json" ]])),

Design Principles | Okta Developer

HTTP/1.1 200 OK
Link: <https://{yourOktaDomain}.com/api/v1/users?after=00ubfjQEMYBLRUWIEDKK>; rel="next",
  <https://{yourOktaDomain}.com/api/v1/users?after=00ub4tTFYKXCCZJSGFKM>; rel="self"