Forum Discussion

RobHess05's avatar
RobHess05
Helper I
5 years ago
Solved

Need help paging in my Power Query

I'm looking to implement paging in my Power Query.  I'm currently querying a REST API, and have successfully built code to authenticate, query & transform the data.  The API only returns a portion of the records, so I need a way to query the entire dataset. 

 

I followed the Microsoft oData paging example, and successfully implemented it with their TripPin API:  https://docs.microsoft.com/en-us/power-query/samples/trippin/5-paging/readme

This API uses the @odata.nextLink field in the body to page thru the data. 
Here's an example:  "@odata.nextLink": "https://services.odata.org/v4/TripPinService/People?%24skiptoken=8"

My API includes the next link field in the header to page thru the data.

Ref:  https://developer.connectwise.com/Best_Practices/Manage_Pagination?mt-learningpath=manage

Here's an example:   <https://staging.connectwisedev.com/v2017_3/apis/3.0/company/companies?pageSize=50&page=2>; rel="next", <https://staging.connectwisedev.com/v2017_3/apis/3.0/company/companies?pageSize=50&page=4>; rel="last"

 

My guess is that I have to extract the header from the Web.Content response, then look for rel="next". 

 

I've been beating on this for a while, but not making any real progress.  If someone else has accomplished this, I would appreciate the help.

Thank you for reading. 

Need Help

2 Replies

    • RobHess05's avatar
      RobHess05
      Helper I
      5 years ago

      Love this solution.  The each statement that allows me to loop thru multiple API calls is key.  
      Here's my code in case someone stumbles across this.  I had to screen capture it from the video, and adjust to make my own 🙂

       

       

      BTG_ManageImplPage = (url as text) =>
    let
        source = Json.Document(Web.Contents(url & "/count", [ Headers = DefaultRequestHeaders ])),
        count = (source[count]),
        pages = List.Numbers(0,count/PageSize, PageSize),
        #"Converted to Table" = Table.FromList(pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
        #"Added Custom" = Table.AddColumn( #"Changed Type", "Custom", each Json.Document(Web.Contents(url & "?pageSize=" & Number.ToText(PageSize) & "&page="&[Column1], [ Headers = DefaultRequestHeaders ]))),
        #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
        
in
    #"Expanded Custom";