getting entries from REST API with 100 entries per chunk

Hi rsieber 

 

Download sample PBIX file

 

For this you would typically use code something like this

 

 

let 
    BaseUrl = "https://{PBX}/api/v1/CallHistory/",
 
    GetCalls = (Index) =>
    
    let 
        Json = Json.Document(Web.Contents(BaseUrl ,
                 [
                     Query = [start = Index]
                ]
            ))
    
    in  Json,
 
 
    CallIndices = List.Numbers( 1, 30, 100 ),   

    Calls = List.Transform(CallIndices, each GetCalls(Number.From(_)))
in
    Calls

 

 

 

The way it works is that you start with a list named CallIndices.  This is a list of numbers starting at 1 and incrementing by 100 each time so you get 1, 101, 201 etc.

 

This is used as the query parameter to the API when you request the call information.

 

List.Transform then calls the GetCalls function for each of these numbers in the CallIndices list, effectively it's requesting information for 100 calls at a time.

 

All of the call info is returned into the object named Calls.  This is a list that can be further manipulated to extract the data you want.

 

Often the API will return some information with each request that indicats if there is more data to get.  Typically you'd check this in the GetCalls function to see if there's more info to retrieve.

 

As I'm not sure how your API works, I've hardcoded CallIndices to hold 30 numbers, all the way up to 2901.  You can either adjust this list to whatever you want, and consequently retrieve as many calls as the list specifies.  Or we can adjust the GetCalls code to stop at a certain point. 

 

But to do this I'd need to see a what the API returns each time.  If the data you have posted above is a complete response from the API, the data named total may indicate that there are a total of 2289 calls available to check.

 

Please try the code in the sample file I've linked to above and let me know how you go.

 

If you want to read more on using API's these may be helpful

 

Getting Started with API's in Power Query • My Online Training Hub

 

Web.Contents - PowerQuery M | Microsoft Docs 

 

regards

 

Phil