Calling the List Enrollment API via Power Query

wayne2
Community Explorer

Hi All,

I am using PowerBI to start to build reports from Canvas data.  I was able to recursively loop through accounts, sub accounts and so forth, including pagination.  However, I hit the enrollments and it only returns the first page.  So, I did some digging, and it appears the enrollments API is what canvas calls an 'expensive' API call so instead of letting you just pass in the next page number, they return a link, in the header, to the next set of data in the query.  

I cannot for the life of me figure out how to successfully do this is Power Query.  Has anyone done this and can point me in the right direction please?  

For reference this is the query.

GET /api/v1/courses/:course_id/enrollments

This is how I solved the others in case it is of help to anyone.  I will use the subaccounts as an example.

Create a function, in this case I called it fSubAccounts

 

= (ParentID as text, Page as number) as table =>
let
    Source = Json.Document(Web.Contents(pURL&"/api/v1/accounts/"&ParentID&"/sub_accounts?access_token="&pAccessToken&"&per_page=100&page="&Number.ToText(Page))),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = ....,
    #"Changed Type" = ...
in
    #"Changed Type"

 

I then add a column to my accounts table via the Invoke Custom Function button under Add Column.  This starts at page one and recursively calls the API (adding 1 to the page number) until it returns a null value.

 

= Table.AddColumn(#"Changed Type2", "SubSubAccountsTable", each (List.Generate(()=>
[Result = try fSubAccounts([Sub_Id],1) otherwise null, Page=1],
each [Result]<>null,
each [Result= try fSubAccounts([Sub_Id],[Page]+1) otherwise null, Page=[Page]+1],
each [Result])))

 

 Hopefully that might help someone

Labels (1)
0 Likes