Dynamically Fetching Dates in Power BI

[TobiasR]

Hi everyone,

I've been working on a Power BI project, and I've managed to set up a code in a blank query that's doing more or less what I need it to do – it's iterating over a predefined set of dates and fetching data accordingly.

Here's the code that I have:

= let
    // List of dates to iterate over
    DatesToFetch = {
        "2024-01-01T00:00:00Z",
        "2024-02-01T00:00:00Z",
        "2024-03-01T00:00:00Z",
        "2024-04-01T00:00:00Z",
        "2024-05-01T00:00:00Z",
        "2024-06-01T00:00:00Z",
        "2024-07-01T00:00:00Z"
    },
    // Fetch data for a given date
    FetchData = (date) =>
        let
            Source = mysource_for_PowerBI.Contents("https://mysource/", null, [date=date]),
            Connections = Source{[Name="Connections"]}[Data],
            #"Project Checklist History" = Connections{[Name="Project Checklist History"]}[Data]
        in
            #"Project Checklist History",
    // Iterates over the list of dates and fetch data for each date
    FetchDataForAllDates = List.Transform(DatesToFetch, each FetchData(_)),
    // Append all fetched data
    AppendedData = Table.Combine(FetchDataForAllDates)
in
    AppendedData

Now, I want to take this a step further. Instead of using this static list of dates, I want to fetch data dynamically based on dates from another table. This table only has a single column containing dates.
The date table was created using this code:

= let
    DateTable = #"Project Checklists",
    DatesToFetch = DateTable[createdAt]
in
    DatesToFetch

And then I converted the list into a table.

Could someone please guide me on how to modify my code to achieve this? Any help or pointers would be greatly appreciated.

Thanks in advance for your assistance!

Tobias

[lbendlin · Accepted solution]

In that second table add a custom column that runs your Web.Contents query for each date.  Then expand that column. Done.