Pagination with List.Generate, however issues when implementing with empty tables

[ljaos67]

Hello everyone. 

So I am trying to implement pagination with List.Generate with data from an API. The issue comes from there is no data for a specific GET request. What I want to do is print the headers with an empty table, but instead the output is an endless loop of empty rows. Here is my code:

----------------------------------------------------------------------------------------------------------------------------

RetrieveAPManualChequeRet Function

----------------------------------------------------------------------------------------------------------------------------

(Page as number)=>
let
Source = CustomCmicConnector.Feed("https://mobile.cmicr12.com/cmicprod/ap-rest-api/rest/1/manualcheck?offset="&Number.ToText(Page)),
RetrieveAPManualCheque1 = Record.ToTable(Source),
Value = RetrieveAPManualCheque1{0}[Value],
#"Converted to Table" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"PaObjectOraseq", "PaCompCode", "PaChqCompCode", "PaChqSeqNum", "PaCurcFactorNum", "PaVouCurcFactorNum", "PaVenCode", "PaPayCurrCode", "PaCurrCode", "PaDraftCode", "PaSelCode", "PaChqNum", "PaPayCurrAmt", "PaPayeeSeq", "PaBchNum", "PaAmt", "PaOldAmt", "Pa1099Code", "PaVouInvCode", "PaVouNum", "PaChqHandleCode", "PaDiscAmt", "PaGlFactorNum", "PaChqObjectOraseq", "PaVouPayCode", "PaVouInvDate", "PaVouDesc", "PaVouInvOutstandAmt", "PaVouHldbkAmt", "PaVouInvAmt", "DiscriminatorCode"}, {"PaObjectOraseq", "PaCompCode", "PaChqCompCode", "PaChqSeqNum", "PaCurcFactorNum", "PaVouCurcFactorNum", "PaVenCode", "PaPayCurrCode", "PaCurrCode", "PaDraftCode", "PaSelCode", "PaChqNum", "PaPayCurrAmt", "PaPayeeSeq", "PaBchNum", "PaAmt", "PaOldAmt", "Pa1099Code", "PaVouInvCode", "PaVouNum", "PaChqHandleCode", "PaDiscAmt", "PaGlFactorNum", "PaChqObjectOraseq", "PaVouPayCode", "PaVouInvDate", "PaVouDesc", "PaVouInvOutstandAmt", "PaVouHldbkAmt", "PaVouInvAmt", "DiscriminatorCode"})
in
#"Expanded Column1"

---------------------------------------------------------------------------------------------------------------------------

Main query

---------------------------------------------------------------------------------------------------------------------------

(url as text) as table =>
let
num = Record.ToTable(CustomCmicConnector.Feed(url & "/ap-rest-api/rest/1/manualcheck")){1}[Value],
Query2 = List.Generate(()=>
[Result = try RetrieveAPManualChequeRet(0) otherwise null, Page = 0],
each [Result]<>null,
each [Result = try RetrieveAPManualChequeRet([Page] + num) otherwise null, Page = [Page]+num],
each[Result]),
#"Converted to Table" = Table.FromList(Query2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"PaObjectOraseq", "PaCompCode", "PaChqCompCode", "PaChqSeqNum", "PaCurcFactorNum", "PaVouCurcFactorNum", "PaVenCode", "PaPayCurrCode", "PaCurrCode", "PaDraftCode", "PaSelCode", "PaChqNum", "PaPayCurrAmt", "PaPayeeSeq", "PaBchNum", "PaAmt", "PaOldAmt", "Pa1099Code", "PaVouInvCode", "PaVouNum", "PaChqHandleCode", "PaDiscAmt", "PaGlFactorNum", "PaChqObjectOraseq", "PaVouPayCode", "PaVouInvDate", "PaVouDesc", "PaVouInvOutstandAmt", "PaVouHldbkAmt", "PaVouInvAmt", "DiscriminatorCode"}, {"PaObjectOraseq", "PaCompCode", "PaChqCompCode", "PaChqSeqNum", "PaCurcFactorNum", "PaVouCurcFactorNum", "PaVenCode", "PaPayCurrCode", "PaCurrCode", "PaDraftCode", "PaSelCode", "PaChqNum", "PaPayCurrAmt", "PaPayeeSeq", "PaBchNum", "PaAmt", "PaOldAmt", "Pa1099Code", "PaVouInvCode", "PaVouNum", "PaChqHandleCode", "PaDiscAmt", "PaGlFactorNum", "PaChqObjectOraseq", "PaVouPayCode", "PaVouInvDate", "PaVouDesc", "PaVouInvOutstandAmt", "PaVouHldbkAmt", "PaVouInvAmt", "DiscriminatorCode"})
in
#"Expanded Column1"

[ImkeF]

Hi @ljaos67 ,
sorry, but I cannot make sense of it.
So hopefully someone else will pick this up.

[ljaos67]

Hello, you were actually right. I had to look at the continue statement and I was able to figure out where the issue was. Thanks!

[ImkeF · Accepted solution]

Hi @ljaos67 ,
to me this looks as if the [Result] actually never gets null, so you might need to find a different function for the continue-parameter.
But in order to determine what is actually going on, you could add a counter-limit to the parameter first and then check the result of the current/latest iteration like so:

(url as text) as table =>
let
num = Record.ToTable(CustomCmicConnector.Feed(url & "/ap-rest-api/rest/1/manualcheck")){1}[Value],
Query2 = List.Generate(()=>
[Result = try RetrieveAPManualChequeRet(0) otherwise null, Page = 0],
each [Result]<>null and [Page] < 100, // or whatever number falls into that range
each [Result = try RetrieveAPManualChequeRet([Page] + num) otherwise null, Page = [Page]+num],
each[Result]),
#"Converted to Table" =...

[ljaos67]

For GET requests that contain at least one record and that have many pages, this function works. The function only doesn't work for tables that don't contain any data at all.