Miguel Escobar Published June 18, 2018

RETRY recursive function in Power BI & Power Query

Power BIPower Query

Recursive functions are a topic that I rarely come across.

To give you some perspective, the instances that I’ve needed recursive functions can be counted with just 1 hand.

This is one of those situations where a recursive function is probably the best way to go.

Big thanks to NicoPer who posted this question on the Official Microsoft Power Query forum.

In short, his scenario is this:


The issue: Connectivity

Sometimes the service from that website is down or his connectivity to the internet fails during a refresh, which pops up a window with the error message and also a button to click on:


Here’s the thing, in order for him to get “the ball rolling” again, he needs to click on that OK button which isn’t that convenient for his case as he just want things to run on automatic as they should be.

The Solution: a RETRY recursive function

For the life of me, I can’t do VBA, so I wasn’t able to give him a solution based on VBA as he was requesting. I’m hoping that a VBA Legend can chime in and hopefully deliver a VBA solution so he can get past that “DataSource.Error” error message.

But before we go in with the Power Query based solution, you can read this article from Daniil Maslyuk about what Recursive functions are. Take in consideration that in this article he states that List.Generate should be used for this scenario, but this specific case on this article is one of those instances where a recursive function is actually the best plan.

so here’s what I ended up doing….

Imagine someone knocking on the door every 10 seconds until someone opens up the door. That’s basically what this recursive function is intended to do and that’s why I want you to be cautious about it since some services will block you if you make too many calls at one point or you could end up in an endless loop. Power Query does have its own security features to prevent endless loops, but they could still potentially happen.

What I ended up creating was a function that will basically try to query the data from that url every 10 seconds until it finally gets some data from it.

The first step was creating the function that will try to get the data from the website.

Call = ()=> Web.Contents(“https://api.binance.com/api/v1/ticker/allPrices”)

The second step is creating that Retry function which ended up looking like this:

fxRetry = (MyCall as function) =>
Buffered = Binary.Buffer (MyCall() ),
Output = if Record.Field(try Buffered , “HasError”) = false then
Buffered else
Function.InvokeAfter(()=>@fxRetry(MyCall), #duration(0,0,0,10) )

Now let’s try to debug that function line by line to see what’s happening:

  • The name of the function is fxRetry
  • it needs a Function as its parameter – we’ll be passing the “Call” function here.
  • In the Buffered step, we use Binary.Buffer to cache the result of the MyCall function.
  • In the Output step we use a try to check if the Buffered step had any errors. If it didn’t have any errors then we’ll just output the result of the Buffered step, otherwise we’ll run the fxRetry function all over again every 10 seconds.

Now that we have the two functions that we needed, we can create another query that will give us the result that we need on our Excel spredsheet and this is the code for that:

Output = let
Source= fxRetry(Call),
ReadBinary = Json.Document( Source ),
ListOfRecordsToTable = Table.FromRecords( ReadBinary ),
ChangedType = Table.TransformColumnTypes(ListOfRecordsToTable,{{“symbol”, type text}, {“price”, type number}})

and voilá! you now have a solution with a retry function.

You can download the workbook from the button below in case you want to see it.

Power BIPower Query
Notify of
8 Comentario
Newest Most Voted
Inline Feedbacks
View all comments
André P.

Thank you, it is working great!

André P.

Hi Miguel,
Where do you recommend to call this function fxRetry? I called it after Web.Contents but before Json.Document, and i thought it worked well but it doesn’t throw errors anymore.

I have it like this:

source = () => Web.Contents(_url, options),
retry = fxRetry(source),
jsonParsed = try Json.Document(retry) otherwise error “This should have worked. Please, try to load a query at a time. Please check if ‘Enable Parallel Loading of Tables’ is off.”,
recordsTable = Table.FromRecords(jsonParsed)

André P.

Oh, i see!
I will adapt for my case now.
Thank you.


Wow, this is amazing, thanks! I added a count parameter to cap the number of retries. Also I was wondering: do you think it is possible to add parameters to the underlying Call to make it dynamic? In your example, for the “Call” function to be Call = (page as text, date as text) => instead of Call () => ?


I’ve actually managed to solve my issue by structuring this a bit differently. Instead of having two functions (Call and fxRetry), I nested the call function within fxRetry, and added parameters to fxRetry, that I can pass when I call the Call function from within fxRetry. Works neat! I struggled a lot with an API which returned a lot of 429 errors, but I think with this it will work fine!! Happy to share my solution if you’re interested.