Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Solved! Go to 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" =...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi @ljaos67 ,
sorry, but I cannot make sense of it.
So hopefully someone else will pick this up.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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!
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" =...
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.