cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RobHess05
Regular Visitor

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". 

RobHess05_0-1620338748179.png

 

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. 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Please see this video for one way to accomplish this.

Power BI - Tales From The Front - REST APIs - YouTube

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User IV
Super User IV

Please see this video for one way to accomplish this.

Power BI - Tales From The Front - REST APIs - YouTube

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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";

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors