Hi,
I hope you can help me. I'm trying to get a list of pages from my company's APIs. APIs give JSON files. I tried to come up with a mix of proposed solutions in here https://www.mattmasson.com/2014/11/iterating-over-an-unknown-number-of-pages-in-power-query/, https://www.sumproduct.com/thought/referencing-multi-page-data-from-a-web-site-with-a-single-url .
So I come up with this, that somehow (don't know much about M) worked:
GetData function to retrieve data from each page
let
Source = (page as number) =>
Json.Document(Web.Contents("https://x/"&Number.ToText(page)))
in
Source
Then I use this function to retrieve a list with the number of pages
let
NumberPages = List.Generate ( ()=>
[Results= try GetData(1) otherwise null, Page = 1],
each [Results] <> null,
each [Results = try GetData([Page]+1) otherwise null, Page = [Page]+1],
each [Results])
in
NumberPages
It did work but never stopped loading data, so I had to stop it because I know I should only have 7 pages. I have a feeling that the List.Generate didn't stop because it gets a list of values for each page (Title, Description, ID, ReturnType, APIURL, Results), so it never finds a "Null" value to stop generating pages?
For each page (1-7) I have to use "Results", which is a List with values (that I convert to table, etc.). However, for page 8 (which should be empty), PBI gives a List with no values in it. If I use Postman Page 8 gives: Results = []. So, I guess that between GetData and Generate.List I should be able to say: retrieve info from each page until Results>List="Empty".
How can I do this? I read this solution which suggests "Record.HasFields" https://community.powerbi.com/t5/Desktop/JSON-with-empty-array/m-p/936709 but I can't get this to make it work as I don't know much about M. Again, I can't change the Generate.List function to say: keep retrieving GetData until you find a page with Results>List=Empty/Null.
Thanks for your help!
