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

[Aregets]

I have 2 endpoints that most of my data comes from that uses "/{Start Date}/{End Date}"

For this I have made a parameter for the {Start Date} to begin where our data starts, and a parameter for {End Date} being "Today()" and just filter the data as needed.

I now added an Endpoint that uses "/{Business Date}" that only pulls single dates.

While trying to figure this out, I have also realized that some of my "/{Start Date}/{End Date}" endpoints request returns no DATE to help reference.

Question 1: Is there a way to pull a request for each "/{Business Day}"? (like a loop?)

Question 2: How would I handle receiving the "/{Start Date}/{End Date}" endpoint that has no DATE reference? Could I also pull each day with solution from Question 1 and conditionally add a DATE column myself?

[Anonymous]

Hi @Aregets ,

Did @danextian  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[danextian · Accepted solution]

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

[amitchandak]

@Aregets , refer if these two can help

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

https://datachant.com/2016/06/27/cursor-based-pagination-power-query/

You can use List.Dates to get dates and use that to create date loop