Power Query Call Function Stop

[Tiger9136]

Hello,

Here is what I have:

I am trying to pull records from a dynamic date range. Depending on the date parameters the records returned could be 100-25000. The API has a limit of 1000 records per call. I was able to make it this far and pull records but when there are no records to return it errors out the remaining tables. I know that if the records returned is less than 1000 then I have reached the end of the data pull.

Question #1: Source = #table({"Offset"}, {{0},{1000},{2000},{3000},{4000},{5000},{6000},{7000},{8000},{9000}}), Can this be modified so that it will continue to increment by 1000 until it the data returned is less than 1000?
Question#2: Stop the CallFunction when the last data pull was less then 1000.

Thanks for any help.


let
#"Code Blue 1" = let
//Table2
Source = #table({"Offset"}, {{0},{1000},{2000},{3000},{4000},{5000},{6000},{7000},{8000},{9000}}),
//CallFunction
CallFunction = Table.AddColumn(Source, "CallFunction", each try Function(Text.From([Offset]))),
//Function
Function = (Offset as text) =>
let
Source = Json.Document(Web.Contents("https://???-???.leading2lean.com/api/1.0/dispatches/?auth=xxxxxxxxxxxxxxxxxxxxxxxx" & "&dispatchtypecode=CodeRed" & "&reported__gte=" &CB1_Month("SpecifyDate","Begin")& "T00:00:00" & "&reported__lte=" &CB1_Month("SpecifyDate","End")& "T23:59:59" &"&limit=1000&offset="&Offset&"")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Record.FieldNames(#"Converted to Table"[Column1]{0}) )
in
#"Expanded Column1"
in
CallFunction,

 

[sandy666]

first try to use CODE tags
[CODE]your code here[/CODE]

 

[Tiger9136]

Sorry, couldn’t edit original post.

let
#"Code Blue 1" = let
//Table2
Source = #table({"Offset"}, {{0},{1000},{2000},{3000},{4000},{5000},{6000},{7000},{8000},{9000}}),
//CallFunction
CallFunction = Table.AddColumn(Source, "CallFunction", each try Function(Text.From([Offset]))),
//Function
Function = (Offset as text) =>
let
Source = Json.Document(Web.Contents("https://???-???.leading2lean.com/api/1.0/dispatches/?auth=xxxxxxxxxxxxxxxxxxxxxxxx" & "&dispatchtypecode=CodeRed" & "&reported__gte=" &CB1_Month("SpecifyDate","Begin")& "T00:00:00" & "&reported__lte=" &CB1_Month("SpecifyDate","End")& "T23:59:59" &"&limit=1000&offset="&Offset&"")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Record.FieldNames(#"Converted to Table"[Column1]{0}) )
in
#"Expanded Column1"
in 
CallFunction,[CODE]