Institutionalized
Accessing World Bank Data with Power Platform Custom Connectors
It came from New Hampshire.
1944. Bretton Woods Conference. New Hampshire, these United States.
World leaders meet to lay the groundwork for a post-WWII economic order. They envision institutions that stabilize currencies, rebuild economies, and fund development across borders. Out of these talks emerge two major financial institutions: the International Monetary Fund (IMF) and the International Bank for Reconstruction and Development, known today as the World Bank.
What began as economic tools for postwar rebuilding evolved into a sprawling repository of global development data. While the US-led Marshall Plan moved fast to rebuild Europe and prevent the spread of Communism, the World Bank played a longer game. In addition to financing reconstruction, the organization worked to rebuild, stabilize, and prevent future global conflict.
Over the years, the Bank has also published a wide range of indicators, tracking everything from literacy and inflation to energy use and income inequality around the world. That’s just the start, yet the information is intended as a signal to its audience of academics, policymakers, analysts and economists, among others: something is coming.
Let’s not forget developers, to whom this type of data can be an essential resource in their line of work. It’s also 100% free.
So that means there’s a catch. And oh, there is.
The World Bank API provides free global development data, but the output requires special handling, otherwise not all data will be returned. But that’s easily fixable, right?
“Send more Pepsi,” someone said.
As it turns out, World Bank endpoints do not return a response that Power Platform custom connectors accept straightaway. Instead, they return a two-layered array: one for pagination metadata, and another for the actual data.
This structure violates Power Platform’s expectations for a usable response schema, triggering errors.
Swagger testing succeeded but actual flows failed. The flow designer failed to parse the structure. Hrmph.
“What have you tried?”
Several things:
Defined schemas in the connector Swagger. Swagger tests succeeded but flows failed.
Wrote C# policies to restructure the array into an object. Flows ran on and on.
Write C# script to restructure the array into an object. Flows ran on and on.
Used HTTP endpoints. This actually worked just fine but for the audience working with this information - banks, financial institutions, etc. - using a custom connector is a best practice.
None of these worked.
Solution
What finally worked: basically, a flow whose structure made that seven-headed beast look like a disgruntled tax accountant.
The solution was not to fight the arrays, but to embrace them.
Essentially, we need to call the connector once to process only the first page of results from the data array while also grabbing metadata to get total result and page counts. Let’s call this the first pass.
Then, a second connector call processes the remaining data pages. Let’s call this the second pass.
An array variable is updated in both passes, and the values from the first pass persist in the second until paging is complete.
Details
First pass:
Second pass:
This wasn’t exactly plug-and-play in Power Platform, yet this approach delivered World Bank data in the end. It can be extended to other endpoints:
What a ride.
Caveats
Things to know before you go.
Licensing. Power Platform premium license required for custom connectors.
Custom connector *not* required. The same result can be accomplished without a custom connector using the HTTP connector. There are specific instances when using a custom connector is advisable, and enhanced security in custom connector is chief among them.
Not bad, this one.
Prerequisites
“System requirements.”
Licensing. Power Platform premium license required if you want to build this custom connector.
Resources and References
Institutionalized (song) - Wikipedia | Suicidal Tendencies (band)
Working with Custom Connectors in Power Platform | Michael Roth