Forum Discussion

LP2803's avatar
LP2803
Responsive Resident
5 years ago

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

  • 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's avatar
      LP2803
      Responsive 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.