Calling a REST API with two parameters, please help

[Wayne74]

Hi,

UPD: I think I worked it out.  Check my reply (yes same person, different account.  I didnt check that first :D).

UPD2: Can confirm that worked.

Firstly let me say I am still very new to Power Query so there is a really good chance I am going about this all wrong.

The scenario:

I am making calls to Canvas to get all of the courses in a sub account and then all of the enrolments against those courses.  Enrollments could be in the hundreds but the per_page limit seems to stop me at 100, so I need to iterate through page numbers of unknown quantity.

So I am trying to do the following:

- Get the list of courses in the sub account

- Get the ID and Name of the course

- Call the Get Enrollments function for each Course ID in the table, for an unknown number of pages

So I have this function (fCourseEnrollments) which works when invoked manually:

= (CourseID as text, Page as number) as table=>

let
Source = Json.Document(Web.Contents("https://[removed].instructure.com",

[RelativePath ="/api/v1/courses/"&(CourseID)&"/enrollments?access_token=[removed]&page="&Number.ToText(Page)])),

I can pass the id in dynamically as a column value and the page number manually and it works

= Table.AddColumn(#"Changed Type", "Table", each fCourseEnrollments([id], 1))

Now this is where I fall over.  I tried to add code to allow me to dynamically call pages until I get a null result.  

 Table.AddColumn(#"Changed Type", "Table", each 
    ([Result = try fCourseEnrollments([id],1) otherwise null, Page=1], 
    each [Result]<>null, 
    each [Result= try fCourseEnrollments([id],[Page]+1) otherwise null, Page=[Page]+1], 
    each [Result]))

 Notice i put everything after the 'each' in enclosed brackets, however its still taking the comma as a parameter into the AddColumn function.

I suspect there has to be a better way to do this and I would really appreciate any help, even just a link to a tutorial or youtube vide etc would be great.

Thank you in advance for any help you can offer

[WayneSingh · Accepted solution]

Ok after havign a break for a few hours and coming back to it I think i just worked it out 🙂  

= Table.AddColumn(#"Changed Type", "Table", each 
    (List.Generate(()=> [Result = try fCourseEnrollments([id],1) otherwise null, Page=1], each [Result]<>null, each [Result= try fCourseEnrollments([id],[Page]+1) otherwise null, Page=[Page]+1], each [Result])))

[WayneSingh · Accepted solution]

Ok after havign a break for a few hours and coming back to it I think i just worked it out 🙂  

= Table.AddColumn(#"Changed Type", "Table", each 
    (List.Generate(()=> [Result = try fCourseEnrollments([id],1) otherwise null, Page=1], each [Result]<>null, each [Result= try fCourseEnrollments([id],[Page]+1) otherwise null, Page=[Page]+1], each [Result])))

[n007jl]

@WayneSingh/@Wayne74 , did you actually get it to work. I did what you did but I am only see data from the first page from the invoked function.

[Wayne74 · Accepted solution]

Sorry @n007jl yes that worked fine.

This is essentially saying add one to the page parameter each time until you get back null results.

= Table.AddColumn(#"Changed Type", "Table", each 
    (List.Generate(()=> [Result = try fCourseEnrollments([id],1) otherwise null, Page=1], each [Result]<>null, each [Result= try fCourseEnrollments([id],[Page]+1) otherwise null, Page=[Page]+1], each [Result])))

However this only works if you API takes page as a parameter.  

Which you can see iun the last line of the function here:

= (CourseID as text, Page as number) as table=>

let
Source = Json.Document(Web.Contents("https://[removed].instructure.com",

[RelativePath ="/api/v1/courses/"&(CourseID)&"/enrollments?access_token=[removed]&page="&Number.ToText(Page)])),

You can test this by running the API in a browser.  So open a browser and go to something like hxxp://[url]/api/[apiname]?access_token=[accesstoken]&page=2

If that doesnt work then your API probably uses a different method to get additional pages.  I have hit one now where the link to the next page is sent back in the header of the request.  I can not for the life of me figure out how to make that one work.