Question

What strategies do you use to avoid redundant transformations in Power Query that slow down data refresh?

I'm working on a Power BI project with large datasets that require frequent updates. However, I've noticed that certain transformations in Power Query are slowing down the data refresh process. I want to streamline these transformations to improve refresh efficiency and avoid redundant steps.

Are there specific techniques or best practices to optimize transformations in Power Query and reduce refresh times?

Answer 1

Explore additional tips to streamline operations and reduce futile steps while continuing the data transformations in Power Query, the most common one being the scenarios where large volumes of data are required to be transformed regularly. This will, in turn, assist in enhancing the effectiveness of data refreshes and also guarantees that your model does not get too big as it expands:

Restrict Unnecessary Transformations: For every transformation step you are carrying out, ascertain that there are no extraneous ones that are contributing to the delays in refresh. Examples of such mistakes would include:

• Calculating the same figure twice.
• Making identical joins after the first one is complete.
• Converting data from one type to another more than once when it is not necessary.

Instead, every operation should be done only once rather than several times, and the results should be used where the processing would be redundant.

Embrace Query Folding: Harness the power of query folding when you can. Query folding is defined as the advancement of as much to the data source for processing transformation steps rather than in Power Query. This is very useful when working with SQL Server, Oracle, or other databases that have native queries. In case you encounter some steps that do contain possible query folding, try to change the order of these transformations or use them at the end of the query.

Prefer the Use of Stepwise Approach: As projects expand in scope, they become more complicated and sometimes even impossible to follow. In as much as Power Query permits multiple steps to be housed in one query, it may make sense to use other queries to manage complex transformations as though they were layers. The advantage of this kind of approach is that a rise in complexity can be managed structurally by breaking the transformations into simpler ones, such as one for cleaning data, another for sub-setting, and the last for all the calculations. It is usually noticeable that the functions of a step being carried out can be separated, which helps in a better understanding of the process and likely causes less time spent refreshing.

Answer 2

Optimizing Refresh Times With These Strategies To Avoid Repetitive Transformations Within Power Query:

When necessary, thus delete redundant steps: take a look at your applied steps and delete duplication; remove unused transformations. In addition, bring similar steps closer together, e.g., Merge columns with added calculated columns into one operation wherever possible.

Encourage Query Folding: Push-back changes to the source whenever possible. Do not break, add other things such as custom columns or some particular M functions, query folding because of using native database actions by working with supported connectors.

Reference Queries: Do not duplicate queries; share queries instead as reference queries. This will reduce redundancy, consolidate base logic, and facilitate updating.

Load out intermediate steps to model and filter at the source to save on the file size of the data set.