How to fetch data page by page from API

[LP2803]

Hi Team,

Im very new to power query and API and unable to figure a way out from this fetching data from API which returns 10 rows per call.

Below is how my code looks like which i got it from some other post and modified for the my requirement.  its giving me error as provided in the image below. Can any one please please help me how i can deal with it.

let
BaseUrl = "https://webexapis.com/v1/meetings?meetingType=meeting&from=2019-01-22T04:00:00Z&to=2020-12-31T04:00:...",
Token = "blahblahblah",
EntitiesPerPage = 10,

GetJson = (Url) =>
let
Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,

GetEntityCount = () =>
let Url = BaseUrl & "$count=true&$top=0",
Json = GetJson(Url),
Count = Json[#"@odata.count"]
in Count,

GetPage = (Index) =>
let Skip = "$skip=" & Text.From(Index * EntitiesPerPage),
Top = "$top=" & Text.From(EntitiesPerPage),
Url = BaseUrl & Skip & "&" & Top,
Json = GetJson(Url),
Value = Json[#"value"]
in Value,

EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table

Error :

[PhilipTreacy]

Hi @LP2803 

In the image you provided the URL shows query parameters like $count and $top  but query parameters should be preceded by an & so the url should look like 

?meetingType=meeting&from=2019-01-22T04:00:00Z&to=2020-12-31T04:00:00Z&count=true&top=0

In the code you provied you also have another query parameter $skip but this should be &skip.

Wherever you are using $ in your query parameters change it to & 

Regards

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.

[LP2803]

Hi Philip,

Thanks a lot for lending me a helping hand.

I did change $ to & and now i have the below error. Kindly help what this error is and how to resolve it.

[LP2803]

@PhilipTreacy  Kindly help. Im really in need of help here.

[mahoneypat]

I looked at their documentation.  Note that you can return 100 records per call instead of the default 10.  Use &max=100 in your url.  Also, i don't see mention of "top", so you may need to use "max" in place of that in your web calls.  Also, why are you using top/max in your count call?

Regards,

Pat

[LP2803]

@mahoneypat Hi, First of all thanks a bunch for taking a look at this. 

Yes. the documentation says i can fetch 100 records at a time with parameter &max=100.

I can also provide &offset=1 (1 is the page number 1) to fetch the records based on the pagenumbers.

However, I being an anlyst require all the data in PowerBI to analyse it. So I need every record from the dataset. so im trying to make API Calls with pagination. I searched online and got this snippet of code and I tried to modify it to my requirement. Still, I dont have idea about few lines in this code as what they actually do (im so naive sorry about it).

It would be really helpful if you could help me to modify this code and help me to fetch all the data.

Please please help.