Hi All,   I am writing a PowerBI dashboard report to pick up the number of total users in our instance, and then later split that by sub-account.  I have the total number working but its so slow and takes about 30-45 min per refresh.  Wont be a problem once published as it will happen overnight, but while I am developing and testing its so painful.  I am hoping the brains trust may have some ideas on how to improve this. So we spoke to Canvas and they essentially use the Last User Access report to guage the number of active users over the last 12 months.  I did some testing and authentication logs dont give me the right information but page views does. So this is what I have Call /api/v1/accounts/[accountid]/users I then go through each user in the table and call /api/v1/users/[userid]/page_views The results are sorted in descending order so I only return the first results (ie, per_page=1) I then filter out the created_at field for anything over 365 days old As I said this works, but its so slow as I am iterating through each user and I cant filter it down first as I dont have any information I can use from the users table to filter on.  If anyone can see an easier way, I would appreciate it. Lastly I will need to then work out what sub-account they belong to.  The obvious answer is look at what course they are enrolled in.  Obviously some may be enrolled in more than 1 course.  Anyway if anyone has any advice that may save me some time before I start on that trail i would greatly appreciate it too. Thank you 🙂 ... View more

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 ... View more