recursion in M Code

[Patto]

Hi Gurus

Alright I have been working on this for ages now and cant crack it.

So let me explain...

I am calling an API which only returns 100 results at a time. 

At the bottom of the resulting json, it provides a number (like a page number but not sequential) that allows me to call the API again and get the next page of results.... and so on and so on...

BUT, what i can not work out, is how to recursively call the api function while passing in the value I received from the previous call.

I could smash this out in C#, or .NET or data factory, but can not seem to get it working in M Code.

So to summarise, I need:

1. call a function i have written to call an API to get the next page number

2. I now need to recursively recall that function passing back the page number from the previous run

3. keep doing this until next page number is null

Please can someone help me, its driving me bonkers!

Cheers

Patto

[mahoneypat]

Another approach instead of using pagination is to simply add a $skip or $skiptoken into URL call.  If your API allows that, you can generate a list of skip values with List.Numbers(0, 10, 100), turn that into a table, change to text and concatenate it into the web call.  You can then expand the tables in that column to get all the results.  Note the middle # is the number of numbers in the list; I put 10 but change it to whatever you need.

Regards,

Pat

[PhilipTreacy]

Hi @Patto 

You need something like this

let

MaxIterations = 10,
url = "https://www.myonlinetraininghub.com/blog/page/",


GetPage=
(PageNum) =>
let
Source = Web.Contents(url & Text.From(PageNum))
in
Source,


GeneratedList =
List.Generate(
() => [i=0, res = GetPage(i)],
each [i] < MaxIterations,
each [i=[i]+1, res = GetPage(i)],
each [res]
)
in
GeneratedList

Without knowing exactly what you are trying to do I can only give a generalised answer. 

List.Generate does the work of looping.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Patto]

Thanks @PhilipTreacy 

I have used that for when the API pages sequentially (ie 1,2,3,4,5,6). Unfortunately one vendor thinks a little bit "differently" and instead of having pages 1,2,3,4,5 they have pages numbered 15464, 25746, 35675, 47865 - more like a page ID instead of a page number.

This means I need to read the page number from the resulting json and then feed that back to call the api with the new page number.... make sense?

its bloody crazy but thats how they have done it.

Any advice??

Cheers

[PhilipTreacy · Accepted solution]

Hi @Patto 

In the function that gets the API data, parse the response for the next page number and return that wih the API data as part of a record.

You can then read that page num from the record in the next call to the function. 

I've removed the i counter from List.Generate as it's not needed, and set the initial condition to [res = GetPage(0)] as that works for me in testing but you will need to set your own inital condition accordingly.

I've also changed the bail out condition in List.Generate to each [res][Next] <> null so it's checking for null in the page number

let

MaxIterations = 10,
url = "https://www.myonlinetraininghub.com/blog/page/",


GetPage=
(PageNum) =>
let
Source = Web.Contents(url & Text.From(PageNum)),

NextPage = ** Parse JSON for Next page Number**,

result = [Data = Source, Next = NextPage]
in
result,


GeneratedList =
List.Generate(
() => [res = GetPage(0)],
each [res][Next] <> null,
each [res = GetPage([res][Next])],
each [res]
)
in
GeneratedList

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Patto]

Thanks @PhilipTreacy

That appears to have done the job.

I think i was using List.Transform originally and was getting the recursive error in power bi but your solutions seems to fit the bill.

Cheers mate.

[PhilipTreacy]

no worries @Patto