RETRY recursive function in Power BI & Power 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:

• He grabs data from this url  –

  https://api.binance.com/api/v1/ticker/allPrices

• Automatic Refresh – he has his workbook open at all times and his query refreshes every minute

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) =>
let
Buffered = Binary.Buffer (MyCall() ),
Output = if Record.Field(try Buffered , “HasError”) = false then
Buffered else
Function.InvokeAfter(()=>@fxRetry(MyCall), #duration(0,0,0,10) )
in
Output

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}})
in
ChangedType

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.