Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
How did you manage to solve it?
Hi @Anonymous
Kindly check below thread whether helps:
https://community.powerbi.com/t5/Desktop/JSON-with-empty-array/m-p/936709
Oh, yes, sorry, Instead of referring to the link that I gave, I was refering to the link you suggested. I will update OP.
Yes, I read that (and others), but couldn't find the way to integrate this into my two functions and considering the Results>List=Empty condition.
Best.
Hi,
Any help on this please?
Thanks.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
68 | |
53 | |
39 | |
33 |
User | Count |
---|---|
70 | |
63 | |
57 | |
49 | |
46 |