List.Generate performance

Question

Hello community!&nbsp;&nbsp;I have a function which calls an API and then loops until all data are retrieved. Therefore, I am using List.Generate, however I am experiencing it to be extremely slow. The number of rows i approx 300.000 and it can take more than 10 minutes to retreive!&nbsp;I have come across the List.Buffer or Table.Buffer which apperarently should enhance the performance, but I don't know where to include it in my query.&nbsp;&nbsp;Can anyone help? 🙂ImkeF&nbsp;maybe?&nbsp;Query:&nbsp;let&nbsp;&nbsp;Kilde&nbsp;=&nbsp;(EndPoint&nbsp;as&nbsp;text,&nbsp;optional&nbsp;Changedate&nbsp;as&nbsp;text,&nbsp;optional&nbsp;PositionOfResultRecordField)=&gt;let&nbsp;&nbsp;&nbsp;&nbsp;//EndPoint&nbsp;=&nbsp;"GetCustomersByLastChange",&nbsp;&nbsp;&nbsp;&nbsp;//PositionOfResultRecordField&nbsp;=&nbsp;1,&nbsp;&nbsp;&nbsp;&nbsp;changedate&nbsp;=&nbsp;if&nbsp;Changedate&nbsp;=&nbsp;null&nbsp;then&nbsp;""&nbsp;else&nbsp;Changedate,&nbsp;&nbsp;&nbsp;&nbsp;positionOfResultRecordField&nbsp;=&nbsp;if&nbsp;PositionOfResultRecordField&nbsp;is&nbsp;null&nbsp;then&nbsp;1&nbsp;else&nbsp;PositionOfResultRecordField,&nbsp;&nbsp;&nbsp;&nbsp;firstwebcall&nbsp;=&nbsp;SinglePageMB(EndPoint,&nbsp;changedate),&nbsp;&nbsp;&nbsp;&nbsp;StartRecord&nbsp;=&nbsp;firstwebcall[results]{0},&nbsp;&nbsp;&nbsp;&nbsp;Pagestoskip_&nbsp;=&nbsp;List.Count(Record.Field(StartRecord,Record.FieldNames(StartRecord){positionOfResultRecordField})),&nbsp;&nbsp;&nbsp;&nbsp;NumberOfPages&nbsp;=&nbsp;Number.RoundUp(StartRecord[entriesleft]/Pagestoskip_),&nbsp;&nbsp;&nbsp;&nbsp;listGenerate&nbsp;=&nbsp;List.Generate(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;()&nbsp;=&gt;&nbsp;[result=StartRecord,&nbsp;resumekey=StartRecord[resumekey],pagestoskip=0,counter&nbsp;=&nbsp;0],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;each&nbsp;[counter]&nbsp;&lt;=&nbsp;NumberOfPages,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;//each&nbsp;[counter]&nbsp;&lt;&nbsp;3,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;each&nbsp;[&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;result&nbsp;=&nbsp;Resumekey_function(EndPoint,[resumekey],&nbsp;changedate){0},&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;counter=[counter]+1,&nbsp;//&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pagestoskip&nbsp;=&nbsp;[pagestoskip]+Pagestoskip_,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;resumekey&nbsp;=&nbsp;try&nbsp;result[resumekey]&nbsp;otherwise&nbsp;null&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;]&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;#"Konverteret&nbsp;til&nbsp;tabel"&nbsp;=&nbsp;Table.FromList(listGenerate,&nbsp;Splitter.SplitByNothing(),&nbsp;null,&nbsp;null,&nbsp;ExtraValues.Error)in&nbsp;&nbsp;&nbsp;&nbsp;#"Konverteret&nbsp;til&nbsp;tabel"in&nbsp;&nbsp;Kilde&nbsp;

mahoneypat · Answer

Please see this approach as an alternative.&nbsp; Also, are you able to increase the number of records returned with each web call?
Power BI - Tales From The Front - REST APIs - YouTube
&nbsp;
Pat
&nbsp;

laura_dannisoe · Answer

Hi&nbsp;&nbsp;I have tried to watch the alternative in the video, but I am not quite sure how to implement that in my solution. Yes I am increasing the number of records. The limit is different for each endpoint in the API.&nbsp;&nbsp;I thought that Buffer could decrease the performance time as it takes around 1,5 hours to refresh the report...&nbsp;

laura_dannisoe · Answer

Also the approach is using GET to my understanding right? Would prefer POST.

imkef · Answer

Hi&nbsp;Laura_Dannisoe&nbsp;,a buffer is useful to stop re-evaluating tables or lists that will be referenced multiple times in iterators (like List.Generate, -.Accumulate, -.Transform or Table.AddColumn).But in your iterator here (List.Generate), there is no direct list or table input (just records or scalars). Therefore you cannot use it here. Does the function you're calling ("Resumekey_function") reference a table or a list? Then you might want to buffer that in there.There are other things to watch out for when optimizing performance:&nbsp;Speed/Performance aspects – The BIccountant&nbsp;maybe you find a hint in there.
&nbsp;

laura_dannisoe · Answer

Hi Imke&nbsp;Thank you for your answer.&nbsp;&nbsp;The resumekey function looks like below - is there anything here I could buffer?&nbsp;letKilde = (EndPoint as text, Resumekey as text, optional Changedate) =&gt;letchangedate = if Changedate = null then "" else Changedate,url = "XXXXXXXXXXXXXX",body = [contract=MB_Agreement,apikey=MB_Key,username=MB_User,password=MB_Password,commands={[command=EndPoint,changedate=changedate,resumekey=Resumekey]}],data = Json.Document(Web.Contents(url,[Headers=[#"Content-Type"="application/json"], Content=Json.FromValue(body), IsRetry=true]))[results]in datainKilde&nbsp;It is just very frustrating that it takes 1,5 hours to refresh and 15 minutes to retrieve data from one table, so I thought there must be a way to handle this faster.

Forum Discussion

List.Generate performance

7 Replies

Related content

List.Generate, make behave like Accumulate or XL REDUCE

Issue with List.Generate

DAX Performance

List.Generate how it updates

Function within List.Generate