The best route to enhance the PBI?

[LithanaM]

Bonjour à tous, 

I have a pretty heavy PBI with many data lines and for which I did many transformations, including three fusions (see below)

When I open the transform data page, I can't access the data on one of my set (the biggest) and have the page below with the "waiting point" going back and forth ( I have been waiting for more than half an hour).

I am a beginner, and I am not sure if I correctly developed my request. I decided not to duplicate my data several time and to put all the transformations in one dataset only. My PBI is much lighter than it was before, but each small change I do entails a huge waiting times when I save (the evaluation time takes forever) or when I open the transform editor.

However, once it's done, the PBI function quite smoothly. 

I am not sure what the best route is to render a lighter PBI.

 - I could get rid of some of the data - is it better to do it on the PBI or should I transform each time the Excel that is produced?

- I need to merge some data - I did everything in the advanced editor (had to summarize using three columns, take the max, and bring back the info on where the max is on each column using again these three references - and I had to do this  three time using a set of different columns each time...). Should I do it differently?

Thanks a lot for your help dear community!

Nathalie 

[v-shex-msft]

HI @LithanaM,

if your query table includes some of the advanced operations, you can consider adding buffer functions to reduce the resource calculation spend on loop each row:

Chris Webb's BI Blog: Improving Power Query Calculation Performance With List.Buffer() Chris Webb's ...

Nested functions and Table.Buffer() - Exceed

Regards,

Xiaoxin Sheng

[lbendlin · Accepted solution]

Merge operations are notorious for being expensive.  Consider if you actually need to merge the tables, or if you can load them as is and then link them in the data model instead.  That is much faster.