PL-300: how to optimize Power Query queries for data preparation

I will teach a skill from the "Prepare the data" domain of the PL-300: how to optimize queries in Power Query (M). This technique is crucial in the exam and in practice because inefficient queries make reports slow, increase costs, and make models harder to maintain.

What you need to know

Power Query (query editor) transforms data before it enters the model. Optimizing means reducing unnecessary work, executing operations at the source when possible, and writing steps that the engine can “fold” (query folding). Query folding is the process by which Power Query converts M steps into the source’s native instructions (for example, SQL) so that the source server executes the work, not your machine.

Example: if you load a SQL table with millions of rows and then apply a date filter and remove columns before loading, ideally those steps are translated into the SQL query and executed on the server. If any step prevents query folding, the entire transformation may run locally, causing slowness.

How it works / Practical key points

Follow a step-by-step to analyze and optimize a query in Power Query.

1. Identify sources that support query folding: SQL databases, Azure SQL, Synapse, OData, and some others support folding. Sources like CSV files typically do not.
2. Apply filters and remove columns early: place filtering and column-removal steps right after the source reference. This reduces data volume and increases the likelihood of folding.
3. Use supported transformations: operations like Filter Rows, Remove Columns, Rename Columns, Group By (depending), and Select Columns typically fold; complex M operations (e.g., custom functions, List.Transform over each row) break folding.
4. Check the query folding status: in Power Query, right-click a step and look for "View Native Query" / "Ver Consulta Nativa". If available, the steps up to that point are folding.
5. Avoid steps that force premature materialization: using Table.Buffer, inserting columns with row-by-row invoked functions, or joining with sources that do not support folding can prevent folding.
6. Combine operations on the server when possible: for SQL sources, prefer that WHERE, SELECT and JOIN are processed on the server. If you need a complex transformation, consider creating a view/CTE in the database and reading that view instead of applying the logic in Power Query.

Simple practical example (M) — filter and pick columns early:

// Passo de ligação à fonte
Fonte = Sql.Database("servidor", "BD"),
Tabela = Fonte{[Schema="dbo",Item="Vendas"]}[Data],
// Aplicar filtro por ano imediatamente
FiltrarAno = Table.SelectRows(Tabela, each Date.Year([DataVenda]) = 2024),
// Remover colunas desnecessárias
Colunas = Table.SelectColumns(FiltrarAno, {"DataVenda","ProdutoID","Valor"})

If you right-click on "Colunas" and there is "View Native Query", the operations above are being converted to SQL on the server. Keep that pattern: filter and reduce columns before heavier operations.

Common mistakes

Here are 3 typical pitfalls.

• Doing complex transformations too early: using custom functions that iterate rows right after the source prevents folding. Instead, try to simplify or move the logic to the source.
• Table.Buffer without necessity: materializing the table locally may seem to solve problems, but it consumes memory and prevents optimizations; use it only when strictly necessary and with small tables.
• Ignoring the View Native Query: many candidates do not check whether steps fold. If a critical step does not have a Native Query, the rest of the chain may also stop folding — reorder or rewrite steps.

How to practice

Practice with sources that support query folding (for example Azure SQL or local SQL Server). Create scenarios: a large table, several M steps and examine when folding is lost. Use right-click to view the native query and change the sequence of steps until folding is preserved.

For exam preparation, use the OFFICIAL free Practice Assessment from Microsoft and the official study guide (also free). These resources show the measured areas and help focus on the skills without resorting to unauthorized material.

In summary

• Query folding pushes work to the source; it is essential for performance with large volumes.
• Apply filters and remove columns immediately to favor folding.
• Check "View Native Query" to confirm steps fold; reorder/rewrite steps if necessary.
• Avoid Table.Buffer and unnecessary row-by-row transformations; when needed, consider moving logic to the source or creating views.