Hand-authored queries workaround for WorldBank data

[AlejandroVazque]

Hi All, 

Appreciate your help.

I'm trying to develop a schedule refresh report in PowerBI, that requires to remove any dynamic data source. This dynamic source comes from my query on World Bank data and the workaround for the pages limitation. In the query here below, you will see how I have to integrate a dynamic query in the tables so I can query for each page of the World Bank data. One option will be to manually query all pages but I will never be sure how many pages the API will deliver ... appreciate if you can help (here below the current query)

let
Source = Xml.Tables(Web.Contents("http://api.worldbank.org/v2/country/all/indicator/NY.GNP.PCAP.PP.CD?date=2000:2022")),
#"Type modifié" = Table.TransformColumnTypes(Source,{{"Attribute:pages", Int64.Type}}),
TotalPages = #"Type modifié"{0}[#"Attribute:pages"],
Personnalisé1 = List.Numbers(1,TotalPages,1),
#"Converti en table" = Table.FromList(Personnalisé1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Type modifié1" = Table.TransformColumnTypes(#"Converti en table",{{"Column1", type text}}),
#"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié1", "Personnalisé", each Xml.Tables(Web.Contents("http://api.worldbank.org/v2/country/all/indicator/NY.GNP.PCAP.PP.CD?date=2000:2022&page="&[Column1]))),
#"Personnalisé développé" = Table.ExpandTableColumn(#"Personnalisée ajoutée", "Personnalisé", {"data", "Attribute:page", "Attribute:pages", "Attribute:per_page", "Attribute:total", "Attribute:sourceid", "Attribute:sourcename", "Attribute:lastupdated"}, {"Personnalisé.data", "Personnalisé.Attribute:page", "Personnalisé.Attribute:pages", "Personnalisé.Attribute:per_page", "Personnalisé.Attribute:total", "Personnalisé.Attribute:sourceid", "Personnalisé.Attribute:sourcename", "Personnalisé.Attribute:lastupdated"}),
#"Personnalisé.data développé" = Table.ExpandTableColumn(#"Personnalisé développé", "Personnalisé.data", {"indicator", "country", "countryiso3code", "date", "value", "unit", "obs_status", "decimal"}, {"Personnalisé.data.indicator", "Personnalisé.data.country", "Personnalisé.data.countryiso3code", "Personnalisé.data.date", "Personnalisé.data.value", "Personnalisé.data.unit", "Personnalisé.data.obs_status", "Personnalisé.data.decimal"}),
#"Personnalisé.data.indicator développé" = Table.ExpandTableColumn(#"Personnalisé.data développé", "Personnalisé.data.indicator", {"Element:Text", "Attribute:id"}, {"Personnalisé.data.indicator.Element:Text", "Personnalisé.data.indicator.Attribute:id"}),
#"Personnalisé.data.country développé" = Table.ExpandTableColumn(#"Personnalisé.data.indicator développé", "Personnalisé.data.country", {"Element:Text", "Attribute:id"}, {"Personnalisé.data.country.Element:Text", "Personnalisé.data.country.Attribute:id"}),
#"Colonnes supprimées" = Table.RemoveColumns(#"Personnalisé.data.country développé",{"Column1", "Personnalisé.data.unit", "Personnalisé.data.obs_status", "Personnalisé.data.decimal", "Personnalisé.Attribute:page", "Personnalisé.Attribute:pages", "Personnalisé.Attribute:per_page", "Personnalisé.Attribute:total", "Personnalisé.Attribute:sourceid", "Personnalisé.Attribute:sourcename"}),
#"Type modifié2" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"Personnalisé.data.date", type date}, {"Personnalisé.data.indicator.Element:Text", type text}, {"Personnalisé.data.indicator.Attribute:id", type text}, {"Personnalisé.data.country.Element:Text", type text}, {"Personnalisé.data.country.Attribute:id", type text}, {"Personnalisé.data.countryiso3code", type text}, {"Personnalisé.data.value", type number}, {"Personnalisé.Attribute:lastupdated", type date}})
in
#"Type modifié2"

[Daryl-Lynch-Bzy · Accepted solution]

Hi @AlejandroVazque - the following step is the cause of the Dynamic Query warning:

#"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié1", "Personnalisé", each Xml.Tables(Web.Contents("http://api.worldbank.org/v2/country/all/indicator/NY.GNP.PCAP.PP.CD?date=2000:2022&page="&[Column1])))

 This step effectively creates a unique URL call.  This makes Power Query think that Credentials are required for each unique call.  To avoid the issue you need to use "RelativePath" feature of Web.Contents - PowerQuery M | Microsoft Learn.  See the example in step one.

To correct the query I would recommend the following two changes:

let
   #"Base URL" = "http://api.worldbank.org/v2/",
   Source = Xml.Tables( Web.Contents( #"Base URL", 
   [
      RelativePath= "country/all/indicator/NY.GNP.PCAP.PP.CD",
      Query = [date = "2000:2022"]
   ] 
   )),

Then

#"Personnalisée ajoutée" = Table.AddColumn(#"Type modifié1", "Personnalisé", each 
  Xml.Tables(
    Web.Contents( #"Base URL",  
        [ 
           RelativePath= "country/all/indicator/NY.GNP.PCAP.PP.CD",
           Query= [ date = "2000:2022", page = [Column1] ]
        ] 
     )
   )
),

This way Power BI will only want to store credentials for the Base URL.

[AlejandroVazque]

Thanks Daryl-LyunchèBzy, the relative path proposal has worked perfectly