Loop several days ( 10 days) in API

[HaiVN]

I have a query to get data through API : 

----
let
Kilde = Json.Document(Web.Contents("https://...startDate=2023-01-01T03%3A00,,,,,,3A00&endDate=2023-01-02T03,,,,,,2B02%3A00&dataset=CONSUMPTION........_HOUR"))

in
Kilde
---

Problem is that API allows me to get data for only 1 day per time. It means that if I want to get data for 10 days i have to use 10 times to do get data from API. How can I use loop with start day and end date whic is 10 days from 2023-01-01.

the next will be: 

----
Kilde = Json.Document(Web.Contents("https://...startDate=2023-01-02T03%3A00%3A00%2B02%3A00&endDate=2023-01-03T03%3A00%3A00%2B02%3A00&dataset=CONSUMPTION_PER_GROUP_MUNICIPALITY_HOUR"))

in
Kilde

---

and continue to next days.

Can any one helps me?

[Angith_Nair]

Hi @HaiVN 

Use the below M Code:

let
    // Define your start and end dates
    StartDate = #date(2023, 1, 1),
    EndDate = #date(2023, 1, 10),

    // Generate a list of dates
    DateList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)),

    // Define a function to get data for each date
    GetDataForDate = (CurrentDate as date) =>
        let
            StartDateTime = DateTime.ToText(DateTime.From(CurrentDate) & #time(3, 0, 0), "yyyy-MM-ddThh:mm:ssZ"),
            EndDateTime = DateTime.ToText(DateTime.From(CurrentDate + #duration(1, 0, 0, 0)) & #time(3, 0, 0), "yyyy-MM-ddThh:mm:ssZ"),
            // Construct the API URL
            URL = "https://...startDate=" & StartDateTime & "&endDate=" & EndDateTime & "&dataset=CONSUMPTION_PER_GROUP_MUNICIPALITY_HOUR",
            // Fetch the data from the API
            Source = Json.Document(Web.Contents(URL))
        in
            Source,

    // Apply the function to each date and combine results into a table
    Data = List.Transform(DateList, each GetDataForDate(_)),
    CombinedData = Table.Combine(Data)
in
    CombinedData

[HaiVN]

@Angith_Nair 
Thank you, but it does not work. I got message and it  shows :

Expression.Error: We cannot apply operator & to types DateTime ang Date.
Detail:
Operator=&
Left=01.01.2023 00:00:00
Right=00:00:00

I wonder this after datetime in the Api : "T03%3A00%3A00%2B02%3A00"

and the format is :  "yyyy-MM-dd'T'HH:mm:ssXXX" , for example: 2023-01-01T03:00:00+02:00