Power Query / API with restrictions

[Manuel123]

Dear Helpers,

I'm new to power query and APIs and I guess that my question is simple to solve for you.

I get energy data from an API from ENTSOE. The queries are limited to a one year range. I'd like to have this year and the last [x] historic years and combine all data in one table. Than I would like to turn this into a function as I would iterate over a table with several country codes. Here is the code example for the API Call for the current year:

let
    Source = 
    
    Xml.Tables(
        Web.Contents(
            "https://transparency.entsoe.eu",
            [
                RelativePath="/api",
                Query=
                [
                    securityToken=#"ENTSOE Key",
                    documentType="A75",
                    processType="A16",
                    in_Domain=ENTSOE_AREA,
                    periodStart="202112312300",
                    periodEnd=Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()),-1),"yyyyMMdd")&"2300"
                ],
                Headers=[#"Content-Type"="application/xml"]
            ]
        )
    )
in
Source

I Hope you can help me

Best regards

Manuel

[mahoneypat]

Does your API return the Country field? If so, do you need to make separate web calls for each country/year combination? In any case, you can start with (or create) a table with start and end datetimes (maybe for each country) and then pass those into your Web.Contents expression by concatenating the column values in as text (or pass them in as parameters in as a function as you suggest).

Pat

[Manuel123]

Dear @mahoneypat,

thank you for your answer and sorry for my late reply. I can iterate a function over the country field. Regarding the date: Here I would like to get the data from 01.01.2020 until the yesterday, whereas yesterday is not fixed but should always roll forward. However, the API does only allow to pull data for 12 months max. Therefore I search for a function that allows to split my api calls in 12 month blocks. Is this possible?

Best regards

Manuel

[mahoneypat · Accepted solution]

If the web call doesn't error, this would be simpler. Just input your desired years as a list and then create columns from Jan 1 to Dec 31 for each year, and then pass that into your webcall.

let
    Source = {2019..2022},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "StartDate", each Date.ToText(#date([Column1],1,1), "yyyyMMdd"), type date),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "EndDate", each Date.ToText(#date([Column1],12,31), "yyyyMMdd"), type date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Column1"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "WebCall", each Xml.Tables(
        Web.Contents(
            "https://transparency.entsoe.eu",
            [
                RelativePath="/api",
                Query=
                [
                    securityToken=#"ENTSOE Key",
                    documentType="A75",
                    processType="A16",
                    in_Domain=ENTSOE_AREA,
                    periodStart=[StartDate],
                    periodEnd=[EndDate]&"2300"
                ],
                Headers=[#"Content-Type"="application/xml"]
            ]
        )
    ))
in
    #"Added Custom2"

Pat