Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Combine Rest API Pagination and Multiple Loops

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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
lbendlin's avatar
lbendlin
Super User
4 years ago

Your function is missing the page number

 

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

in
source
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi. Yes already tried the above function but it does not let me invoke the function. Instead shows error

    .and also it does.nott work on blow query for pagination,

     

    = list.generate (()=>

    [Output = try Factivity (1) otherwise null, n=1]

    Each [output] <> null,

    Each [output = try Factivity ([n]+50) otherwise null, n=[n]+50],

    Each [output])

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      I think use function List.Generate in Power Query is a good way.

      How to use List.Generate to make API Calls in Power Query M

      Instead of pagination, you can also use List.Numbers along with Skip or Offset in the web calls to overcome the row limit.  This video walks through it.

      Power BI - Tales From The Front - REST APIs - YouTube

       

      Best Regards,
      Rico Zhou

       

      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

        I have tried list. generate finction but it does not provide values. Also I have watched the tales from front video but it requires count of the API rows which i don't have.