Web.Content calling API service and merging pages with List.Transform started to fail

Dear all,

I created PowerBI report which which is connecting to data source via API service. Returning json contains thousands of entities. API service is called via Web.Content function. API service returns always total record count and so we are able to calculate nr. of pages which has to be called to obtain whole dataset. This report is displaying data from our servisdesk app, which is deployed on many servers and for many customers and use Query parameters to connect to any of these servers.

Detail of Power query is below.

Why am I writing here. This report was working without any issue more than 1,5 year but on August 17th one of servers start causing erros in step Pages where are some random lines (pages) with errors - see attached picture labeled "Errors in step Pages". and this is reason that next step Entities (List.Union) in query is stopping refresh and generate errors with message:

- Expression.Error: We cannot apply field access to the type List. Details: Value=[List] Key=requests

What is notable

• API service si returning records in the same order but faulty lists are random when calling with same parameters  
  • some times is refresh without any error
• the same power query called on another server is working correctly , problem is only with one specific server
• this problem started without notice on the most important server after 1,5 year withou any problem

Here is full text power of query for this main source, which is used later in other queries to extract all necessary data. Json is really complicated and I extract from it list of requests, list of solvers, list of solver groups,.... and this base query and its output is input for many referenced queries.

let
    BaseAPIUrl = apiurl&"apiservice?", /*apiurl is parameter - name of server e.g. https://xxxx.xxxxxx.sk/ */
    EntitiesPerPage = RecordsPerPage, /*RecordsPerPage is parameter and defines nr. of record per page - we used as optimum 200-400 record per pages, but is working also with 4000 record per page*/
    ApiToken = FnApiToken(), /*this function is returning apitoken value which is returning value of another api service apiurl&"api/auth/login", which  use username and password in body of call to get apitoken */
    
    GetJson = (QParm) =>  /*definiton general function to get data from data source*/
        let 
            Options = 
            [   Query= QParm,
                Headers=
                [
                    Accept="application/json", 
                    ApiKeyName="apitoken", 
                    Authorization=ApiToken
                ]
            ],
            RawData = Web.Contents(BaseAPIUrl, Options),
            Json    = Json.Document(RawData)
        in  Json,

    GetEntityCount = () =>  /*one times called function to get nr of records using GetJson, which is returned as a part of each call*/
        let 
            QParm = [pp="1", pg="1" ], 
            Json  = GetJson(QParm),
            Count = Json[totalRecord]
        in  
            Count,

    GetPage = (Index) =>  /*repeatadly called function to get each page of json using GetJson*/
        let 
            PageNr  = Text.From(Index+1),
            PerPage   = Text.From(EntitiesPerPage),
            QParm = [pg = PageNr, pp=PerPage],
            Json  = GetJson(QParm),
            Value = Json[data][requests]
        in  Value,

    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),  /*setup of nr. of records to variable*/
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),  /*setup of nr. of pages */
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_) /*Function.InvokeAfter(()=>GetPage(_),#duration(0,0,0,1))*/),  /*here we call for each page GetJson function to get whole dataset - there is in comment test with delay between getpages but was not neccessary*/
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table
====================================================================================
================================================================================
here is another way of of appending pages to list which I used as alternative solution lookong for reason of fails 

   Source = List.Generate(    /*another way to generate list of all pages*/
        ()  =>  [Page = 0, ReqPageData = GetPage(0) ],
        each [Page] < PageCount, 
        each [ReqPageData = GetPage( [Page] ),
                Page =  [Page] + 1 ], 
        each [ReqPageData]
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), /*here i am able to generate table from list in contrast when is used List.Generate*/
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"), /*here aj can expand list to column*/
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Column1", {"Column1"}) /*here i try to exclude errors, but i dont know what happend and which records (if any) are excluded*/
in
    #"Removed Errors"

 Errors in step Pages

I also tried another way of appending pages to list using List.Generate (see in code at the end divided by ===========. This is also bringing random errors in list but

• it is bringing possibility to transform to table in contrast with original way with using List.Transform
• but other referenced querries are failing and contains on the last row errors

When I am exploring content of faulty page/list extracting it via Add as New Query there are always all record withou any fail.....

Extracting errored page

and finnaly I am tottaly clueless not able to find the cause of this behavior on this specific server. I tested to call pages which are errored via POSTMAN,  I discused this issue with author of API service and He also tried to call this API service with all parameters but server is returning every page OK, only Power query is not able to List.Transform ... 

I will be gratefull and apreciate any tips or advices or if somebody solved the same issue in the past ....

Kuby