Forum Discussion

Wayne74's avatar
Wayne74
Helper I
3 years ago

PowerQuery API Pagination Help

Hi All,

 

I am really stuck here at the moment and just need a pointer or hint or something to know where to look.  I am calling an API from Canvas to get accounts, courses and enrollments.  Accounts and courses is fine as I can loop through use the page=x parameter.  However Enrollments changes that.  The link to the next page is in the header under something called Link and uses something called a bookmark.  I can not for the life of me work out how to make this work as I cant seem to see the 'link' anywhere in Power Query.

 

So this is a link to the API documentation.  The one I am looking at is List Enrollments

Canvas LMS REST API Documentation (instructure.com)

 

If I call this in Postman I can see this (I have cut out most of the data for obvious reasons)

 

So I got this to try to see the header data

 

 

 

= (CourseID as text) =>

let
    Source = Value.Metadata(Web.Contents(pURL&"/api/v1/courses/"&CourseID&"/enrollments?access_token="&pAccessToken))
in
    Source

 

 

 

Which gives me this

 

and then if I expand the Headers record I get this.  Nothing in here that shows the bokmark or the Link field

 

Any help would be greatly appreciated please.

3 Replies

  • You asked for Value.MetaData - that will not give you the payload of the request which has the data and pagination information.

     

    Note that your approach will not work for scheduled refresh.  Read about RelativePath.

    • Wayne74's avatar
      Wayne74
      Helper I

      I have looked into Relative path and I am fixing that thank you.  Still haven't worked out how to get the header yet though with the link to the next bookmark.

      • lbendlin's avatar
        lbendlin
        Super User

        Use Web.Contents directly.  That should give you the header data and the payload.