Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
srpandya95
Regular Visitor

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

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
beforeThis is the cursor that points to the start of the page of data that has been returned.
afterThis is the cursor that points to the end of the page of data that has been returned.
limitThis 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"
7 REPLIES 7
gabrielsroka
Frequent Visitor

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.

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

 

Hi @srpandya95,

 

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

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

Specializing in Power Query Formula Language (M)
srpandya95
Regular Visitor

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"

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.