Power BI/Query - How to pull many-date API requests

Hi Aregets 

 

You will need a table that has start and end date columns and the API query to be parameterized and references those date columns. The query also needs to use Web.Contents relativepath option otherwise it will create a dynamic data source which cannot be refreshed in the service.

 

Below is a sample custom column formula that picks data from Open-Meteo and doesn't require an API key. The actual formula may vary depending on the API requirements

 

let
    BaseUrl = "https://archive-api.open-meteo.com/v1/archive",
    StartDate = Date.ToText([Start], "yyyy-MM-dd"),
    EndDate = Date.ToText([End], "yyyy-MM-dd"),
    QueryParameters = [
        latitude = "52.52",
        longitude = "13.41",
        start_date = StartDate,
        end_date = EndDate,
        hourly = "temperature_2m",
        timezone = "Asia/Singapore"
    ],
    Headers = [
    
        #"Accept-Encoding" = "gzip, identity",
        #"Accept-Language" = "en-GB,en;q=0.5"
    ],
    FullResponse = Web.Contents(
        BaseUrl, 
        [
            Query = QueryParameters,
            Headers = Headers
        ]
    )
in
    Json.Document(FullResponse)

 

Below is the complete M Script you can play around with.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzTSNzIwMlHSUQKyYJxYnWglQ2MkOSMDhFwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each let
    BaseUrl = "https://archive-api.open-meteo.com/v1/archive",
    StartDate = Date.ToText([Start], "yyyy-MM-dd"),
    EndDate = Date.ToText([End], "yyyy-MM-dd"),
    QueryParameters = [
        latitude = "52.52",
        longitude = "13.41",
        start_date = StartDate,
        end_date = EndDate,
        hourly = "temperature_2m",
        timezone = "Asia/Singapore"
    ],
    Headers = [
    
        #"Accept-Encoding" = "gzip, identity",
        #"Accept-Language" = "en-GB,en;q=0.5"
    ],
    FullResponse = Web.Contents(
        BaseUrl, 
        [
            Query = QueryParameters,
            Headers = Headers
        ]
    )
in
    Json.Document(FullResponse))
in
    #"Added Custom"

 

If you aren't familiar with using the Web.Contents options, you can use ChatGPT to convert a url string to a Web.Contents formula that uses relative path and references table columns for the parameters.  This is the url string from open-meteo

https://archive-api.open-meteo.com/v1/archive?latitude=52.52&longitude=13.41&start_date=2024-12-19&end_date=2025-01-02&hourly=temperature_2m&timezone=Asia%2FSingapore