Optimize Refresh from Dataflow

[joshua1990]

I have a Dataflow (Gen1) that contains a large volume of data. Both columns and rows. I have already profiled the data to delete redundant and unnecessary data.
Now I would like to further optimize the refresh.
In fact, the data in the dataflow is only the sales of the last 3 years. This data is updated weekly with the sales of the previous week.
What concepts / approaches are there to further optimize the refresh?

[OktayPamuk80]

Hi Joshuha1990,

One thing to consider, did you already have followed good data processing techniques? What is your data source? You might benefit a lot from query folding. Additionally follow the processing iteration:

1. Filter out all unrelevant records and columns
2. Append before join
3. Change to correct data types early
4. get rid of duplicates
5. If on table is used multiple times, consider Table.Buffer() (need to check, sometimes it's not useful at all)
6. For Joins consider SortMerge method (Check this guys blogs, they are incredible: https://blog.crossjoin.co.uk/2020/06/07/optimising-the-performance-of-power-query-merges-in-power-bi...)

I don't know, what data source and whether query folding would be available. However, putting some transformations before, results in running those in your source system, from which you can benefit a lot.

Regards,

Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[HashamNiaz]

Hi @joshua1990 !

Please try to setup an incremental load to optimize your data load rather than full load;

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure

Regards,

Hasham