API limited to 50 responses - pagination looping required

[monkeysocks]

Hi,

I hope that you are well. I am really struggling to find a solution to pull more than 50 records from an API endpoint. Essentially I need to keep looping the call until all rows have been pulled.

I have found plenty of examples for Json but am struggling for Xml, any help to get this to dynamically loop until all records have been pulled rather than using the offset argument (as per the developer docs) with a static row number would be greatly appreciated.

Current Query:

let
    Source = Xml.Tables(Web.Contents("https://vcc-eu10b.8x8.com/api/stats/agents")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"agent-id", type text}, {"group-id", Int64.Type}, {"date-hired-date", type text}, {"direct-agent-access", type text}, {"email-address", type text}, {"enabled", type text}, {"first-name", type text}, {"full-name", type text}, {"last-name", type text}, {"phone-used", type text}, {"workplace-phone", type text}, {"phone-extension", Int64.Type}, {"login-id", type text}, {"user-rights", type text}, {"voice-mail-activated", type text}, {"agent-to-agent-chat-disabled", type text}, {"max-concurrent-customer-chat", type text}, {"deleted", type logical}}),
    #"Expanded workplace-SIP" = Table.ExpandTableColumn(#"Changed Type", "workplace-SIP", {"Element:Text"}, {"workplace-SIP.Element:Text"})
in
    #"Expanded workplace-SIP"

Source:

Expanded:

API developer documentation:
Pagination

Requests that would return a large number of records will respond with 50 records per request. You can iterate through these response sets by setting the {offset} argument. In this example, we are asking for agent interactions, starting with the 101st record.

http://vcc-na4.8x8.com/api/stats/agent/rws/interactions?n=101

"VCC APIs have a max return of 50 entries. To get the next 50 entries, the API request will have to be modified to return rows starting at 51.

For example: https://vcc-eu2.8x8.com/api/stats/queues?n=51"


Thanks in advance for any assistance or guidance

[mahoneypat]

Please see this video for how to do this.

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

Pat

[monkeysocks]

Thank you very much Pat, this is a really great video. I have 1 slight issue which is probably me looking in the wrong area or doing the wrong thing (so apologies in advance for my ignorance). I can only receive as Xml or CSV not Json. So when I create the query my source looks like the below and does not provide the pagination info like in the video, so I have nothing at present to base the required number of iterations off. 

What should I be doing differently to pull that info?

Thanks in advance

[mahoneypat · Accepted solution]

Hard to know without being able to drill into that table to see if there is an overall count in there, or if there is a separate type of call you can make to get the count (e.g., /$count").  If you know how many records to expect another way, you can just make your List.Numbers go past that and then have a Remove Errors step to get rid of the ones that went too far, before you expand the data.

Pat

[monkeysocks]

Hi Pat,

There wasn't a call available for the count so I went with the List.Numbers and Remove Errors Option and it worked perfectly. Thank you very much for your help!