Use a list of countries

[WillY_OneEyed]

Hi guys,

I need to bring all "GDP (current US$)" indicator of all countries using powerquery:

let
    Source = List.Accumulate(
        {1..10},
        {},
        (s,c) => s & Json.Document(Web.Contents("https://api.worldbank.org/v2/country/USA/indicator/NY.GDP.MKTP.CD/?format=json&page=" & Number.ToText(c))){1}
    ),
    #"Table From Records" = Table.FromRecords(Source)
in
    #"Table From Records"

Here's the problem. I need change "USA" for a list of countries.

Ty folks

[v-jianboli-msft · Accepted solution]

Hi @WillY_OneEyed ,

You can modify your Power Query code to loop through a list of countries and fetch the "GDP (current US$)" indicator for each country. First, you need to create a list of country codes. Then, you can use

List.Accumulate to iterate through the list of countries and fetch the data for each country. Here's an example of how you can modify your code:

let
    CountryList = {"USA", "GBR", "FRA", "DEU"}, // Replace this with your list of country codes
    Source = List.Accumulate(
        CountryList,
        {},
        (s, countryCode) => s & List.Accumulate(
            {1..10},
            {},
            (s2, c) => s2 & Json.Document(Web.Contents("https://api.worldbank.org/v2/country/" & countryCode & "/indicator/NY.GDP.MKTP.CD/?format=json&page=" & Number.ToText(c))){1}
        )
    ),
    #"Table From Records" = Table.FromRecords(Source)
in
    #"Table From Records"

Replace CountryList with your list of country codes, and the modified code will fetch the "GDP (current US$)" indicator for each country in the list.

Besides, here is a detailed document about how to use world bank api:

API Document (worldbank.org)

Hope it would help you.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.