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

    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.

    • v-yingjl's avatar
      v-yingjl
      Community Support
      4 years ago

      Hi Anonymous ,

      In this case seems like it belongs to the 'page number' is known so you may refer this video to do the pagination for the API which introduces it in details:

      How to do Pagination on a REST API in Power Query (Part 1) 

       

      When the 'page number' is unknown, it has better to use List.Generate(), see:

      How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate 

       

      Best Regards,
      Community Support Team _ Yingjie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        H v-yingjl  if you look at me reply already applied the step. but it only works for single id not for multiple id's.

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      4 years ago

      Since you have the query below working with pagination with a hard-coded Id value, you just need to convert it to a function that accepts the Id value as an input.  You would then start with your list of Id values and invoke it to get a List of 50-row tables on each row, which you could expand to new rows, and then expand the column of tables.

       

      Sorry I didn't have time to propose the new function using the pieces of M code throughout.

       

       

      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

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