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 url like this,
PFB,
https://vcc-na8.8x8.com/api/stats/agents/{agent-id}/activities?


so in order to extract data of all agent-id's at once i used this function below,


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


Than i used the invoke function against agent id's to extarct each id succesfuly into new table.

BUT

The issue is that each id provides 50 rows only and than it moves to next agent-id,
PFB,

Is there a way to create or manipulate a function in such a way that it provides a pagination as well.

Solution required:
Keeping in mind that API does not provide any info. when loaded into power bi.
I need some one to guide or manipulate a current in such a way that i can extract data of all id's and complete data (more than 50 rows) in one request.

Thanks in advance.



Need Help
Show and Tell
Tips and Tricks

8 Replies

  • 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

      Hi Phil thanks for the reply. Huge fan of your youtube channel.

      PFB the API documentation,
      https://bit.ly/3vyczJk

      PFB the API response both in browser and in Power query,
      Browser,

      in Power Query,


      Proof that there is more than 50 rows per id's,
      So I created this function and query to extract all rows from one id,

      let
      source=(n as number)=>
      let
      Source = Xml.Tables(
      Web.Contents(

      "https://vcc-na8.8x8.com/api/stats/agents/ag3ZcvfXhXSYW2xDU3VfOYkw/activities?n="&Number.ToText(n)

      )
      )
      in
      source

      in
      source



      Proof that there is more than 50 rows, (I have manually ckecked the last row, API does not provide any information about the count)

      Query to convert it into table,

      let
      Source =

      List.Generate(

      ()=>
      [output =

      try activity(1) otherwise null, n=1],

      each [output]<> null,

      each [output = try activity([n]+50) otherwise null, n=[n]+50],

      each [output]

      )

      in
      Source

       

      BUT                                                                   

      When i use the below query to extract all id's instead of one, it gives me 50 rows per id because im unable to do pagination in below function and query using the above pagination method, 
      PFB the complete query which extracts all id's,

      (id as text)as table=>


      let
      Source = Xml.Tables(

      Web.Contents(

      "https://vcc-na8.8x8.com",

      [RelativePath=

      "/api/stats/agents/"

      &(id)&

      "/activities?&n="
      ]
      )
      )
      Next steps that converts it into table
      1. Create a custom table with all id's,

      2. Invoke custom function against these id's with that function,

       


      3. than it gives me each id but with 50 rows only and thats the issue I WANT AL THE ROWS WITH ALL THE ID'S

       

      I hope the problem and requirmement is clear. let me know if you need anything else,
      sorry for the long reply but really stuck in this issue and i need to resolve this.
      best,
      Saad.

    • 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.