Power query API calls fail (sometimes)

[GerhardA]

  fisrtCallAmount = "40000",

  callAPI = (url as any) as record =>
        let       
            ...
            source = Json.Document(Web.Contents("https://mri_api", [RelativePath=relPath])),
            
            results = try source[value] otherwise null,
            nextURL = try source[nextLink] otherwise null,

            cleanedURL = 
              if odata = null 
              then nextURL
              else Replacer.ReplaceText(nextURL, "%24", "$"),
            
            return = [results=results, next=cleanedURL]
        in
            return,

The above query is used in a list generator to get all the data from the database, from MRI using their API service Looking for help connecting to MRI SaaS data source, a few records at a time. In this case the page size is 40000.

This query works... sometimes. It is suppose to pull 450k+ records but it just pulls some factor of 40000, in other words at some point 'source' gets a null value, where it should be getting data, and ends the list generation. There are also times where the query would fail on the first call

I am unable to see if MRI is indeed sending the data through for those calls or if its Power BI thats cutting out.

Decreasing the page size has variable results. Im currently testing with 10000 but a successful run takes short of 50min where 40000 takes just over 20min So I would prefer to use 40000.

I have tried this in both pbi desktop and dataflows.

I have also tried doing a bunch of retries:

source_1 = Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath])),
source_2 = if try source_1[value] otherwise null = null 
              then Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath]))
              else source_1,
source_3 = if try source_2[value] otherwise null = null 
              then Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath]))
              else source_2,

            source = source_3,

This just returns an empty list each time and I couldnt be bothered to figure out why this isn't working.

So I then tried to clean this up to get the below query. only to get a cyclic refernence error. 

let
  maxCalls = 5,
  getSource = (counter as number, relPath as any) as any => 
    let
        source = Json.Document(Web.Contents("https://mrix5api.saas.mrisoftware.com/MRIAPIServices", [RelativePath=relPath])),
        srcValue = try source[value] otherwise null,
        return = if srcValue = null and counter < maxCalls
          then getSource(counter +1, relPath)
          else source
    in
      return
in
  getSource

BUT the below recusrive call works. 🤷🏼‍ using '@' didnt get rid of the error for me.

 getSource = () =>
    let
      Source = try #"API Function"("method_name") otherwise null,
      return = if Source = null then getSource() else Source
    in
      return,

I also tried the Function.InvokeAfter and increased the timeout on the Web.Contents to avail.

Has anyone else come accross this issue? or know how to resolve it.

[PhilipTreacy]

Hi @GerhardA 

For debugging purposes, change your function to return the entirety of source so you can see what the API returns when it stops/fails. 

As it is when source[value] doesn't exist, you end up with null, but that doesn't mean that nothing at all was sent by the API, it should be returning somethign for each call.

Also, what is odata an where is its value set?

Regards

Phil