Loop though passed dates

[JeffGrant]

Hi All,

I have a requirement to loop the following code through the past 120 days. When I do this manually, everything works fine. However, I need to run this for the last 120 days. I could sit at my PC and changed the system date 120 times, but I figure there must be a better way and I can also expand my learning.

I am thinking that the "Result" would be the accumulation of all of the downloaded data.

How would I structure this code to achieve this? There is a lot more to this code, that is not pasted here.


// URL constants
Today = Date.ToText(Date.From(DateTime.LocalNow()), [Format="dd-MMM-yyyy"]),


// API Loop
TableFromJson = Table.AddColumn(WithRaceNumbers, "Json", each
let
URL = BaseDomain & "/GetFormHistorical/" & [Track] & "/" & Text.From([RaceNumbers]) & "/" & Today & "?ApiKey=" & APIKey,
Result = Json.Document(Web.Contents(URL))
in
Result
),


thanks in advance

 

[ExcelToDAX]

You can iterate through a table of dates. For example, here is a Power Query code to get a list of dates from Today to 120 days ago. You can now reference the column "DateText" instead of the Today function you are utilizing.

let
    Date120Days = Date.AddDays(Date.From(DateTime.LocalNow()),-120),
    CurrentDate = Date.From(DateTime.LocalNow()),
    Dates =  List.Distinct(List.Transform({Number.From(Date120Days)..Number.From(CurrentDate)}, each Date.StartOfDay(Date.From(_)))),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "DateText", each Date.ToText([Column1], [Format="dd-MMM-yyyy"])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"})
in
    #"Removed Columns"

 

[JEC]

let 
    dates = List.Transform(List.Dates(Date.From(DateTime.LocalNow())-#duration(120,0,0,0),120,#duration(1,0,0,0)), each Date.ToText(_,"dd-MMM-yyyy")),
    out = Table.FromList(dates, Splitter.SplitByNothing(), null, null)
in
    out

 

[JeffGrant]

Thank you for your contribution. I am showing my age now because I simply cannot get me head around this syntax. It is so different to what I grew up with.
I have tried unsuccessfully to get this syntax correct. I appreciate that ExceltoDAX indicates that all I need do it reference the column "DATETEXT" instead of the Today function, however it is throwing a referencing error. I don't know, but would the DateText variable assigned in the first loop for "GetMeetingList" lose its value in the second nested loop for "GetHistoricalForm"?

The code that I need to iterate through is this. Please see my comments in Green.

Today = Date.ToText(Date.From(DateTime.LocalNow()), [Format="dd-MMM-yyyy"]),
APIKey = "123456"
BaseDomain = "https://helloworld",

// Main source URL
URL = BaseDomain & "/GetMeetingList/" & Today & "?ApiKey=" & APIKey, <- The code needs to get this GetMeetingList with the first date. This is the part that goes back 120 days.

// Main source - Json.Document returns a record
Source = Json.Document(Web.Contents(URL)),

// Actual sort is the Result field - extract the list
Result = Source[Result],

// Generate main table from the extracted list, it still contains records
Table = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

// Expand the records to get the actual data table finally
Tracks = Table.ExpandRecordColumn(Table, "Column1", {"Date", "DateStamp", "MeetingId", "RaceCount", "RaceNumbers", "Resulted", "State", "TABMeeting", "Track", "TrackId"}),

// Expand the RaceNumbers list values as new rows. This generates necessary number of rows for each track
WithRaceNumbers = Table.ExpandListColumn(Tracks, "RaceNumbers"),

// Fetch the JSON as the new table column
TableFromJson = Table.AddColumn(WithRaceNumbers, "Json", each
let
URL = BaseDomain & "/GetFormHistorical/" & [Track] & "/" & Text.From([RaceNumbers]) & "/" & Today & "?ApiKey=" & APIKey,
Result = Json.Document(Web.Contents(URL)) <- Then loop through this GetHistoricalForm API call, then go back and get the next Meeting List for the next date, and then loop through this API call again.
in
Result
),


etc etc

Thanks in advance.

 

[JEC]

I can't test your query but here is a syntax to accumulate over a list of dates.
The first step is just creating the list of dates. The second step is using that list to add columns with just the date as record, one by one.

let 
    dates = List.Transform(List.Dates(Date.From(DateTime.LocalNow())-#duration(120,0,0,0),120,#duration(1,0,0,0)), each Date.ToText(_,"dd-MMM-yyyy")),
    loop = List.Accumulate(dates, "", (s,c) => if s = "" then #table(null,{{"a",c}}) else Table.AddColumn(s,c, each c))
in
    loop