Power Query hangs when iterating paginated API

[Anonymous]

Dear community,

  I'm having issues when querying paginated API of our chatbot solution. As per the documentation the API should be queried in a was as follows

1. Get the first response with default parameter in the API call. Something like this: 

{url}/v2/assistants/{assistant_id}/logs?version=2021-06-14

2. Check the response for presence of cursor token. If it does exist then there is more than one API page to be iterated through

{response}[pagination][next_cursor]

3. Add a cursor token from the previous step as new parameter to API call

{url}/v2/assistants/{assistant_id}/logs?version=2021-06-14&cursor={value of next_cursor}

4. Loop the API call with the same value of cursor token, collect the response and check it for presence of [pagination][next_cursor] element. Stop looping when not present.

I've implemented the above logic in Power Query. It works for the first and second iteration but when I add one more then it stops responding, PowerBI looks like collecting the data but no result at all, even when I left it running for half an hout. Each page has 100 rows and the first two pages are collected within a second. I also tried to limit the amount of data by transforming the response to a table with limited set of columns but no change. Here is my code

let
    uriStatic1 = "https://{url}/api",
    uriStatic2 = "/v2/assistants/{id}/logs?version=2021-06-14",
    uriCursor = "&cursor=",
    header = [Authorization="Mr. Auth Token"],
    uriStatic = uriStatic1 & uriStatic2,
    cursor = try Json.Document(Web.Contents(uriStatic, [Headers=header]))[pagination][next_cursor] otherwise null,
    initData = Json.Document(Web.Contents(uriStatic, [Headers=header]))[logs],
    initDataTbl = Table.FromList(initData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    initDataTblExpand = Table.ExpandRecordColumn(initDataTbl, "Column1", {"log_id", "request", "response_timestamp", "assistant_id", "request_timestamp", "response", "session_id", "language", "skill_id", "customer_id"}, {"Column1.log_id", "Column1.request", "Column1.response_timestamp", "Column1.assistant_id", "Column1.request_timestamp", "Column1.response", "Column1.session_id", "Column1.language", "Column1.skill_id", "Column1.customer_id"}),
    initDataTblExpandIdx = Table.AddIndexColumn(initDataTblExpand, "Index", 0, 1, Int64.Type),     
    mrIterator = 0,
    idxPointer = Table.RowCount(initDataTblExpandIdx),
    getDataPages = (data as table, cursor as text) =>
        let
            mrIterator = mrIterator + 1,
            newUri = uriStatic1 & uriStatic2 & uriCursor & cursor,
            newData = Json.Document(Web.Contents(newUri, [Headers=header]))[logs],
            newDataTbl = Table.FromList(newData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
            newDataTblExpand = Table.ExpandRecordColumn(newDataTbl, "Column1", {"log_id", "request", "response_timestamp", "assistant_id", "request_timestamp", "response", "session_id", "language", "skill_id", "customer_id"}, {"Column1.log_id", "Column1.request", "Column1.response_timestamp", "Column1.assistant_id", "Column1.request_timestamp", "Column1.response", "Column1.session_id", "Column1.language", "Column1.skill_id", "Column1.customer_id"}),
            newDataTblExpandIdx = Table.AddIndexColumn(newDataTblExpand, "Index", 0, 1, Int64.Type),               
            newCursor = try Json.Document(Web.Contents(newUri, [Headers=header]))[pagination][next_cursor] otherwise null,
            data = Table.Buffer(Table.Combine({data, newDataTblExpandIdx})),
            resultSet = if mrIterator = 1 then data else @getDataPages(data, cursor)
        in 
            resultSet,
    Source = if cursor = null then initDataTblExpandIdx else getDataPages(initDataTblExpandIdx, cursor)         
in
    Source

Long story short, when I change the condition 

resultSet = if mrIterator = 1 then data else @getDataPages(data, cursor)

to 

resultSet = if mrIterator = 2 then data else @getDataPages(data, cursor)​

then it hangs. 

Any idea what I'm doing wrong?

Thank you,

Martin

[Anonymous · Accepted solution]

Ok, solved. The source of my problem was caching. For the second iteration futher PowerBI read the result from cache instead from my web source. This was caused by the API paginaton logic that expects to create the very same call again and again until the response says "stop". With PowerBI caching feature this would never happen as the same call returns the same result from cache.   

File -> Options and settings -> Options -> Data Load -> Data Cache managenet options

Setting the value of Maximum allowed (MB) = 0 fixed my issue