Calling the List Enrollment API via Power Query

[wayne2]

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

[chriscas]

Hi @wayne2,

While I'm not familiar with PowerBI, after a bit of googling it seems like retrieving the response header with the links may not be possible without building a "custom connector" (but again, I don't know how to do that).  The main thing I wanted to point out is that if you're iterating over every course in your Canvas instance to get enrollments, it's going to be a lot of API calls (even if you have a relatively small Canvas instance).  You may want to look in to using the provisioning report API to retrieve all enrollments.  It's generally a better/faster way if you're looking to get the entire dataset for your instance.  I don't know how easy this would be to implement in PowerBI, but I thought I'd give it a mention so you could check it out.  I use that method with some python scripts I wrote and it made things way faster than using the enrollments API course by course (and I wasn't even looking up every course).

-Chris

[wayne2]

Sorry mate. I only just realised I hadnt come back to you. Yes I basically gave up on this and started trying another approach that didnt include an 'expensive' API. Takes a lot longer to run but it was the only option I could see. Still havent finished but gotten further.