PL-300: como otimizar consultas Power Query para preparação de dados
Vou ensinar uma competência do domínio "Preparar os dados" do PL-300: como otimizar consultas em Power Query (M). Esta técnica é crucial no exame e na prática porque consultas ineficientes tornam relatórios lentos, aumentam custos e tornam mais difícil a manutenção dos modelos.
O que precisas de saber
Power Query (editor de consultas) transforma dados antes destes entrarem no modelo. Optimizar significa reduzir trabalho desnecessário, executar operações na fonte quando possível e escrever passos que o motor pode “fold” (query folding). Query folding é o processo pelo qual o Power Query converte passos M em instruções nativas da fonte (por exemplo, SQL) para que o servidor de origem execute o trabalho, não o teu computador.
Exemplo: se carregas uma tabela SQL com milhões de linhas e logo aplicas um filtro por data e removes colunas antes de carregar, o ideal é que esses passos sejam traduzidos para a query SQL e executados no servidor. Se algum passo impedir query folding, toda a transformação pode ficar a correr localmente, causando lentidão.
Como funciona / Pontos-chave práticos
Segue um passo-a-passo para analisar e optimizar uma consulta em Power Query.
- Identifica fontes com suporte a query folding: bases SQL, Azure SQL, Synapse, OData, e algumas outras suportam folding. Fontes como ficheiros CSV geralmente não suportam.
- Aplica filtros e elimina colunas cedo: coloca passos de filtragem e remoção de colunas logo após a referência à fonte. Isso reduz o volume de dados e aumenta a probabilidade de folding.
- Usa transformações suportadas: operações como Filter Rows, Remove Columns, Rename Columns, Group By (dependendo), e Select Columns tipicamente foldam; operações complexas em M (ex.: funções personalizadas, List.Transform sobre cada linha) quebram o folding.
- Verifica o estado do query folding: no Power Query, clica com o botão direito num passo e procura "View Native Query" / "Ver Consulta Nativa". Se estiver disponível, o passo até aí está a foldar.
- Evita passos que forçem a materialização prematura: usar Table.Buffer, inserir colunas com funções invocadas linha a linha, ou juntar com fontes que não suportam folding pode impedir o folding.
- Combina operações no servidor quando possível: para origem SQL, prefiras que o WHERE, SELECT e JOIN se processem no servidor. Se precisares de uma transformação complexa, considera criar uma vista/CTE na base de dados e ler essa vista em vez de aplicar a lógica em Power Query.
Exemplo prático simples (M) — faz filtro e escolhe colunas cedo:
// 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"})
Se clicares com o botão direito em "Colunas" e houver "View Native Query", as operações acima estão a ser convertidas para SQL no servidor. Mantém esse padrão: filtrar e reduzir colunas antes de operações mais pesadas.
Erros comuns
Aqui tens 3 armadilhas típicas.
- Fazer transformações complexas cedo: usar funções personalizadas que iteram linhas logo após a origem impede o folding. Em vez disso, tenta simplificar ou mover a lógica para a origem.
- Table.Buffer sem necessidade: materializar a tabela localmente pode parecer resolver problemas, mas consome memória e impede optimizações; usa-o só quando estritamente necessário e com tabelas pequenas.
- Ignorar o View Native Query: muitos candidatos não verificam se os passos foldam. Se um passo crítico não tiver Native Query, o resto da cadeia também pode deixar de foldar — reordena ou reescreve passos.
Como praticar
Pratica com fontes que suportem query folding (por exemplo Azure SQL ou SQL Server local). Cria cenários: uma tabela grande, vários passos M e examina quando o folding se perde. Usa o botão direito para ver a consulta nativa e altera a sequência de passos até que o folding se mantenha.
Para preparação do exame, utiliza o Practice Assessment OFICIAL e gratuito da Microsoft e a study guide oficial (também gratuita). Estes recursos mostram as áreas medidas e ajudam a focar-se nas competências sem recorrer a material não autorizado.
Em resumo
- Query folding envia trabalho para a fonte; é essencial para desempenho com grandes volumes.
- Aplica filtros e remove colunas imediatamente para favorecer o folding.
- Verifica "View Native Query" para confirmar que os passos foldam; reordena/reescreve passos se necessário.
- Evita Table.Buffer e transformações linha-a-linha desnecessárias; quando for preciso, considera mover lógica para a origem ou criar vistas.