As an administrator and developer, it is important to know how to avoid those and include good performance measures when working on those M queries in Power BI. Below are the challenges most commonly encountered.
Using Step Transformations More Than Normal
- Challenge: In Power Query, each transformation step of the query works on the result of the preceding transformation. Omitting some transformation steps may negatively affect performance due to the excessive number of unnecessary steps implemented in any given transformation query.
- Solution: Team-up changes are often seen as separate (say, filtering and sorting) and need intermediate changes that serve no purpose. Where applicable, don’t just have multiple transformations; combine them where possible.
Loading More Data Than You Need
- Challenge: A power query often pulls in all the records in the dataset before it performs any transformations or filtering. This, of course, comes with problems, including poor performance due to large volumes of data.
- Solution: Includes restrictions very early in the transformation, processing an even smaller dataset. For instance, removing rows or keeping rows can already be among the first actions in your query to lessen the amount of data available for changes before other manipulations.
Maintaining Query Folding In Contrasting Cases:
- Challenge: on the other hand, is Anything done in Power Query, such as carrying out manual steps, performing custom transformations, and using functions that could be query folding friendly? As a result, the Power query wants to bring all the data and do the processing, which could be more efficient.
- Solution: Use transformations that enable query folding, such as filtering, joining, or aggregating, at the data source itself. Do not introduce non-foldable operations, e.g., row-by-row transformations, at the beginning of the query. To check whether folding is occurring, right-click a step and check if ‘View Native Query’ appears.
Avoiding Data Buffering:
- Challenges: There are instances when Power Query goes back to re-evaluating a few steps of the process (for example, heavy transformations or joins), which negatively affects performance.
- Solution: Employ the Table.Buffer function whenever time constraints are detrimental to the performance of a certain table during query execution. This is done to avoid evaluating the same table more than once.
Applying Complex Custom Functions at the Beginning:
- Challenges: Custom-made functions executed early in the process, particularly those that affect iteration over rows, are notably performance-killing.
- Solution: Only alter the dataset with such complex functions once it has already been reduced because it has been filtered. Utilize such functions only after the dataset has been pre-filtered or aggregated and already simplified.
Take note of these pitfalls, and This will enhance the data transformation process and performance when using Power BI.
