Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Looping to Multiple id's and pagination in API using Power Query

Hi everyone I hope you all are fine. Any power query expert here in rest API's Really need some help. I'm stuck for days.  Background: I am extracting data from an rest api which has get request...
Need Help
Show and Tell
Tips and Tricks
PhilipTreacy's avatar
PhilipTreacy
Super User
4 years ago

Hi Anonymous 

Yes this can be done but to do so you need to examine the response sent by the API to see if there are more records to fetch. The response should contain a field that tells the query if there is more data for that particular ID.

Can you please provide a sample API response so I can check it to see how paging works for it.

Or do you have documentation for the API that explains the same ?

Any chance you can share your full query code so I can work with that by making API requests?

Regards

Phil

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Also. The pagination should be in a way that it refreshed in power bi service aswell.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    HI Everyone. I hope you all are having a wonderful day.
    Background:
    I want extract all data from Rest API but the issue is that its URL provides data of 50 rows per Agent-ID.
    URL:  https://vcc-na8.8x8.com/api/stats/agents/{Agent-ID}/activities?n=1
    So far I've been able to create a function in Power query which allows me to extract all the ID's,
    PFB,

    (id as text) as table=>
    let
    Source = Xml.Tables(
    Web.Contents(
    "https://vcc-na8.8x8.com",
    [RelativePath=
    "/api/stats/agents/"
    &(id)&
    "/activities?n="]
    )
    ),

    in
    source
    Than I invoked this function against the Agent id's column in new table and received al the id's with 50 rows.

    Issue:
    It provides 50 rows per ID. I need to do a pagination in above function so that it gives me all of the rows with these all Id's.

    PFB the API documentation.
    https://bit.ly/3hBkoWE

    Let me know if someone has the way to solve my problem.