Showing results for 
Search instead for 
Did you mean: 
Regular Visitor

Power query API calls fail (sometimes)


  fisrtCallAmount = "40000",

  callAPI = (url as any) as record =>
            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]


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("", [RelativePath=relPath])),
source_2 = if try source_1[value] otherwise null = null 
              then Json.Document(Web.Contents("", [RelativePath=relPath]))
              else source_1,
source_3 = if try source_2[value] otherwise null = null 
              then Json.Document(Web.Contents("", [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. 



  maxCalls = 5,
  getSource = (counter as number, relPath as any) as any => 
        source = Json.Document(Web.Contents("", [RelativePath=relPath])),
        srcValue = try source[value] otherwise null,
        return = if srcValue = null and counter < maxCalls
          then getSource(counter +1, relPath)
          else source


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


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



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.

Super User III
Super User III

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?



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

Blog :: YouTube Channel :: Connect on Linkedin

Proud to be a Super User!

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!


Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors