Power Query - Parallel or Concurrent REST Invocation

[dbeavon3]

This question is about a Power Query (M) script that I'm trying to write.
I'd like to be able to start with a list of items, and make a REST/GET call to retrieve some associated JSON for each item.

The problem is that the initial list has ~100 items in it and the REST method takes ~45 seconds each. This means that if I get the JSON sequentially for each item, it takes well over an hour to complete. That is excessively long, and isn't making full usage of the underlying resources (disk, cpu, RAM, and network are all under utilized).  The REST api seems to be primarily bottlenecked on a bit of CPU, but never more than a single core/thread of a single back-end server.

I'd like to call those REST methods concurrently, either with parallel execution, or with an asynchronous callback. I'd like results to be retrieved with some high degree of concurrency, like ten at a time. There is only about 1 MB of data returned in each of these JSON document items.

This REST method is in a third-party API, and I cannot change it. But there is plenty of excess/unused capacity available on the back-end. As long as Power Query client is capable, there should not be any problem if we would make ten or even all (100) calls to the REST method at the same time. I'm hoping that power query can accomplish this, and that it can accumulate the results into a final table.

I haven't found any approach that would allow me to solve this problem in a Power Query script. The only solution I envision is to create another intermediate resource (custom software hosted in a service, or azure function, etc) where some of the concurrency-related behavior would be orchestrated. It seems unfortunate to have to place a dependency on another software resource, simply for the sake of calling a REST API in a concurrent loop 100 times.

Any suggestions would be greatly appreciated.  I may be overlooking some feature of Power BI where this type of work could be accomplished (maybe custom connectors?)  My search results haven't turned up much information on this topic.  Ideally there would be an approach that could be extended to other sorts of data as well (not only for REST API's.)

[dbeavon3]

Thanks for the reply.

It sounds like I'll have to move some of the orchestration out of power query altogether.

I recently started using the "data flows" in the PBI service.  They seem a bit underwhelming.  They could be a lot more useful if they could gather data concurrently from multiple connections. That seems like a pretty obvious requirement for larger models.

But I haven't found too many discussions on this particular subject. So I'm betting the people who need it are generally capable of finding a workaround. The most obvious workaround is to host another additional tier of logic that manages the parallel work, and keeps the results cached in memory. That cache would then be used for spoonfeeding power BI in rapid and serialized way, as required by power query.

On a side note, adding this functionality into power query wouldn't necessarily need to be implemented with parallel threads per-se. It could be done in a less sophisticated way with async/await completion contracts (like they do in single-threaded javascript.) That approach could work just as well as parallel threads, since most REST api's can take time to respond (and power query is otherwise sitting around doing absolutely nothing).

[artemus]

What you requested would require a new API. There are currently many users who expect to only have 1 request at a time going to their web server.

You are free to build your own connector and do this, but as I said, it would be at your own risk.

Another option is to use Azure Data factory.

[artemus · Accepted solution]

Parellel operations are not permitted within a single query.

Parellel operations are not supported within a custom connector.