Continue List.Generate() even when API request is Null to Allow for Incremental Refresh

[Sherod]

I have created a function to query an api and am now trying to implement Incremental Refresh. I am using List.Generate() to paginate through the api. The api allows me to use a query based on the eventDate. I am attempting to implement the Incremental Refresh option in Power BI so that I can only load the the most current event dates as old dates are not changing. However, with my query code, there are some dates where the api returns an empty value and when the List.Generate() encounters these dates where there are no events, the List.Generate stops the loop. I'm hoping to have List.Generate() ignore the null queries against the api and conintue until the RangeEnd date has been reached. 

Function

(Offset as number, endpoint as text, ext as text, eventDate as datetime)=>
let
    token_url = tokenUrl,    
    body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    Source = Json.Document(Web.Contents(token_url,
    [
        Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
        Content = Text.ToBinary(body)
    ]
    )
    ),
    token = Source[access_token],
    data = Json.Document(Web.Contents("https://edfiapi.nefec.org/v5.2.0/DS/api",
    [
        Headers = [#"Authorization"="Bearer "&token,#"Content-Type"="application/json"],
        RelativePath="/data/v3/"&ext&"/"&endpoint,
        Query = [#"limit"="100",#"offset"=Number.ToText(Offset),#"eventDate"=Text.Replace(Date.ToText(DateTime.Date(eventDate)), "/","-")]
    ]
    )
    )
    ,#"Data" = data
in
    #"Data"

Table M Query

let
    Source = List.Generate(()=> [Result = fApiDate(0,endpoint,ext,eventDate), offset=0, endpoint="studentSchoolAttendanceEvents", ext="ed-fi", eventDate=RangeStart],
                                each ((not (List.IsEmpty([Result]))) or (List.IsEmpty([Result]))) and ([eventDate]<=RangeEnd),
                                each [Result = fApiDate([offset], [endpoint], [ext], [eventDate]), offset=[offset]+100, endpoint=[endpoint], ext=[ext], eventDate=Date.AddDays([eventDate], 1)],
                                each [Result]),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "schoolReference", "sessionReference", "studentReference", "attendanceEventCategoryDescriptor", "eventDate", "attendanceEventReason", "_etag"}, {"id", "schoolReference", "sessionReference", "studentReference", "attendanceEventCategoryDescriptor", "eventDate", "attendanceEventReason", "_etag"})
in
    #"Expanded Column2"

[Anonymous]

How do you deal with blank results for a given eventDate value? I have a similar script and if there are no records,

#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1")

will cause an error as there is no Column1. 

[Sherod]

@artemus Thank you for your reply. I did attempt to try the List.Select as a wrapper around the List.Generate call and while doing so, I stumbled upon the realization that List.Generate wasn't just stopping when it received back an empty list. The problem wound up being that I had the code set to both add 100 to the offset and also add a day on each loop. This resulted in each day that was queried to already have an offset other than 0 and so after a few days passed, the offset was so high that the query to the api always returned an empty set. To fix the issue, I adjusted my code to account for this. See below. Thank you again for your help. 

Function

(Offset as number, endpoint as text, ext as text, eventDate as datetime)=>
let
    token_url = tokenUrl,    
    body = "Client_id="&clientKey&"&Client_secret="&clientSecret&"&Grant_type="&grantType,
    Source = Json.Document(Web.Contents(token_url,
    [
        Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
        Content = Text.ToBinary(body)
    ]
    )
    ),
    token = Source[access_token],
    data = Json.Document(Web.Contents("https://edfiapi.nefec.org/v5.2.0/DS/api",
    [
        Headers = [#"Authorization"="Bearer "&token,#"Content-Type"="application/json"],
        RelativePath="/data/v3/"&ext&"/"&endpoint,
        Query = [#"limit"="100",#"offset"=Number.ToText(Offset),#"eventDate"=Text.Replace(Date.ToText(DateTime.Date(eventDate)), "/","-")]
    ]
    )
    )
    ,#"Data" = data
in
    #"Data"

Table

let
    Source = List.Generate(()=> [Result = fApiDate(0,endpoint,ext,eventDate), offset=0, endpoint="studentSectionAttendanceEvents", ext="ed-fi", eventDate=RangeStart],
                                each [eventDate] <= RangeEnd,
                                each if 
                                        List.IsEmpty([Result]) then
                                            [Result = fApiDate([offset], [endpoint], [ext], [eventDate]), offset = 0, endpoint=[endpoint], ext=[ext], eventDate=Date.AddDays([eventDate], 1)]
                                            else [Result = fApiDate([offset], [endpoint], [ext], [eventDate]), offset = [offset]+100, endpoint=[endpoint], ext=[ext], eventDate=[eventDate]] ,
                                each [Result]
                
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id", "sectionReference", "studentReference", "attendanceEventCategoryDescriptor", "eventDate", "_etag"}, {"id", "sectionReference", "studentReference", "attendanceEventCategoryDescriptor", "eventDate", "_etag"}),
    #"Expanded sectionReference" = Table.ExpandRecordColumn(#"Expanded Column2", "sectionReference", {"localCourseCode", "schoolId", "schoolYear", "sectionIdentifier", "sessionName"}, {"localCourseCode", "schoolId", "schoolYear", "sectionIdentifier", "sessionName"}),
    #"Expanded studentReference" = Table.ExpandRecordColumn(#"Expanded sectionReference", "studentReference", {"studentUniqueId"}, {"studentUniqueId"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Expanded studentReference", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Removed Duplicates" = Table.Distinct(#"Removed Blank Rows"),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Duplicates",{{"id", type text}, {"localCourseCode", type text}, {"schoolId", Int64.Type}, {"schoolYear", Int64.Type}, {"sectionIdentifier", type text}, {"sessionName", type text}, {"studentUniqueId", type text}, {"attendanceEventCategoryDescriptor", type text}, {"eventDate", type datetime}, {"_etag", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [eventDate] >= RangeStart and [eventDate] <= RangeEnd)
in
    #"Filtered Rows"

[artemus]

You could wrap your List.Generate call with a List.Select call which filters out the rows that don't have a date.