Forum Discussion
How to fetch data page by page from API
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:00Z",
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 :
5 Replies
- PhilipTreacySuper User
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=0In 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.- LP2803Responsive Resident
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.
- LP2803Responsive Resident
PhilipTreacy Kindly help. Im really in need of help here.