cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laura_Dannisoe
Frequent Visitor

List.Generate performance

Hello community! 

 

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!

 

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. 

 

Can anyone help? 🙂

@ImkeF maybe?

 

Query:

 

let
  Kilde = (EndPoint as text, optional Changedate as text, optional PositionOfResultRecordField)=>
let
    //EndPoint = "GetCustomersByLastChange",
    //PositionOfResultRecordField = 1,
    changedate = if Changedate = null then "" else Changedate,
    positionOfResultRecordField = if PositionOfResultRecordField is null then 1 else PositionOfResultRecordField,
    firstwebcall = SinglePageMB(EndPoint, changedate),
    StartRecord = firstwebcall[results]{0},
    Pagestoskip_ = List.Count(Record.Field(StartRecord,Record.FieldNames(StartRecord){positionOfResultRecordField})),
    NumberOfPages = Number.RoundUp(StartRecord[entriesleft]/Pagestoskip_),
    listGenerate = List.Generate(
        () => [result=StartRecord, resumekey=StartRecord[resumekey],pagestoskip=0,counter = 0],
        each [counter] <= NumberOfPages,
        //each [counter] < 3,
        each [
            result = Resumekey_function(EndPoint,[resumekey], changedate){0},
            counter=[counter]+1,
 //           pagestoskip = [pagestoskip]+Pagestoskip_,
            resumekey = try result[resumekey] otherwise null
        ]
    ),
    #"Konverteret til tabel" = Table.FromList(listGenerate, Splitter.SplitByNothing(), nullnull, ExtraValues.Error)
in
    #"Konverteret til tabel"
in
  Kilde

 

7 REPLIES 7
mahoneypat
Super User
Super User

Please see this approach as an alternative.  Also, are you able to increase the number of records returned with each web call?

Power BI - Tales From The Front - REST APIs - YouTube

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Hi 

 

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. 

 

I thought that Buffer could decrease the performance time as it takes around 1,5 hours to refresh the report... 

Hi @Laura_Dannisoe ,
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: Speed/Performance aspects – The BIccountant maybe you find a hint in there.

 

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 Imke

 

Thank you for your answer. 

 

The resumekey function looks like below - is there anything here I could buffer?

 

let
Kilde = (EndPoint as text, Resumekey as text, optional Changedate) =>
let
changedate = 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 data
in
Kilde

 

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.

Hi @Laura_Dannisoe ,
no, cannot spot a table or a list to buffer in here.
Did you experience the performance degradation just today? Is it in dataflows in a Europe region?

Then this might just be a temporary problem: Slow Dataflow Refresh - Microsoft Power BI Community

Otherwise: How many other queries are there in your file/dataflow?

 

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 Imke 

No I have experienced this ever since I did the API calls. Some dataflows only have 1 query while others have 5 queries. 

I have tried to optimize the performance by reducing the columns in each table, but that did not improve anything, so I am quite sure it is the function calling the API which is extremely slow. When I call the API in another system it is not that slow. 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors