getting entries from REST API with 100 entries per chunk

[rsieber]

Hello,

I'm quite new with PowerBI and facing a big problem:

I try to get the call history from my PBX using a Rest API:

http(s)://{PBX}/api/v1/CallHistory/

First off all I get the last 100 entries as response:

{
  "type": "result",
  "result": {
    "total": "2289",
    "records": [
      {
        "id": "8289",
        "start": "2015-03-11 13:32:36",
        "answer": "2015-03-11 13:32:40",
        "end": "2015-03-11 13:32:42",
        "from_number": "320",
        "to_number": "320",
        "from_name": "u_320",
        "to_name": "u_320",
        "billsec": "2",
        "disposition": "ANSWERED",
        "lastapp": "Queue",
        "userfield": "",
        "faxdata": "",
        "lastdata": "tags:yellow,green, blues",
        "cost": "",
        "channel": "SIP/320-000000aa",
        "dstchannel": "SIP/320-000000ab",
        "type": "call"
      },
      {
        "id": "8288",
        "start": "2015-03-11 13:32:36",
        "answer": "2015-03-11 13:32:36",
        "end": "2015-03-11 13:32:40",
        "from_number": "320",
        "to_number": "",
        "from_name": "u_320",
        "to_name": "",
        "billsec": "0",
        "disposition": "NO ANSWER",
        "lastapp": "Wait",
        "userfield": "",
        "faxdata": "",
        "lastdata": "15",
        "cost": "",
        "channel": "SIP/320-000000aa",
        "dstchannel": "SIP/320-000000ab",
        "type": "call"
      }
    ]
  }
}

 If I want the next 100 entries I need to use:

http(s)://{PBX}/api/v1/CallHistory/?start=101

and so on.

Now I have no idea how to get this working in PowerBI. Hopefully sb. can give me the right direction.

Thanks in advance,

Robert

[v-henryk-mstf]

Hi @rsieber ,

Whether the advice given by @PhilipTreacy  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[PhilipTreacy · Accepted solution]

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