Práticas recomendadas ao trabalhar com o Power Query

Estas práticas recomendadas do Power Query ajudam você a melhorar o desempenho da consulta, aproveitar o query folding, escolher os tipos de dados corretos, organizar as transformações e reutilizar a lógica com parâmetros e funções personalizadas. Elas aplicam-se tanto ao Power Query Desktop quanto ao Power Query Online.

Escolha o conector certo

Power Query oferece muitos conectores de dados. Esses conectores vão desde fontes de dados, como arquivos TXT, CSV e Excel, até bancos de dados como Microsoft SQL Server e produtos saaS (software como serviço), como Microsoft Dynamics 365 e Salesforce. Se um conector criado com finalidade não estiver disponível na janela Obter Dados , use um conector genérico, como ODBC ou OLE DB.

Escolha o conector criado com finalidade para sua fonte de dados quando um estiver disponível. Por exemplo, o conector SQL Server fornece uma melhor experiência de Obtenção de Dados do que o conector ODBC genérico quando você se conecta a um banco de dados SQL Server. O conector do SQL Server também oferece suporte a recursos de desempenho, como delegação de consulta. Para saber mais, acesse Visão geral da avaliação da consulta e da dobragem de consultas em Power Query.

Cada conector de dados segue uma experiência padrão, conforme explicado na obtenção de dados. Essa experiência padronizada tem um estágio chamado Visualização de Dados. Nesta fase, você receberá uma janela amigável para selecionar os dados que deseja obter da fonte de dados, se o conector permitir e uma visualização de dados simples desses dados. Você pode até mesmo selecionar vários conjuntos de dados da fonte de dados por meio da janela Navegador .

Filtrar dados antecipadamente para melhorar o desempenho

Filtre os dados o mais cedo possível para reduzir o número de linhas que o Power Query processa nas transformações posteriores. Para conectores que dão suporte ao dobramento de consulta, o Power Query pode aplicar os filtros diretamente na fonte de dados, conforme descrito em Visão geral da avaliação e do dobramento de consulta no Power Query. Filtrar dados irrelevantes também limita os dados mostrados na visualização de dados.

Use o menu de filtro automático, que mostra uma lista distinta dos valores encontrados em sua coluna, para selecionar os valores que você deseja manter ou filtrar. Use a barra de pesquisa para ajudá-lo a encontrar os valores em sua coluna.

Você também pode aproveitar os filtros específicos por tipo, como anterior para uma coluna de data, data e hora ou até mesmo de fuso horário.

Esses filtros específicos de tipo podem ajudá-lo a criar um filtro dinâmico que sempre recupera dados que estão no número x anterior de segundos, minutos, horas, dias, semanas, meses, trimestres ou anos.

Deixe as operações custosas por último para melhorar o desempenho

Para melhorar o desempenho de visualização no editor de Power Query, execute operações caras por último. Determinadas operações exigem a leitura da fonte de dados completa para retornar quaisquer resultados e, portanto, são lentas para visualizar. Por exemplo, se você executar uma classificação, é possível que as primeiras linhas classificadas estejam no final dos dados de origem. Para retornar quaisquer resultados, a operação de classificação deve primeiro ler todas as linhas.

Outras operações (como filtros) não precisam ler todos os dados antes de retornar resultados. Em vez disso, eles operam sobre os dados de uma maneira conhecida como "processamento contínuo". Os dados "fluem" continuamente, e os resultados são retornados ao longo do processo. No editor do Power Query, essas operações só precisam ler o suficiente dos dados de origem para preencher a visualização.

Quando possível, execute essas operações de streaming primeiro e faça as operações mais caras por último. Executar operações nesta ordem ajuda a minimizar o tempo gasto aguardando a visualização ser renderizada sempre que você adicionar uma nova etapa à consulta.

Usar um subconjunto de dados durante o desenvolvimento de uma consulta

Se a adição de novas etapas no editor do Power Query for lenta, use Manter Primeiras Linhas para limitar os dados processados enquanto você desenvolve a consulta. Depois de adicionar todas as etapas necessárias, remova a etapa Manter Primeiras Linhas para que a consulta concluída processe o conjunto de dados completo.

Usar os tipos de dados corretos

Defina o tipo de dados correto para cada coluna para que Power Query possa disponibilizar transformações e filtros específicos do tipo. Por exemplo, quando você seleciona uma coluna de data, pode usar as opções no grupo de colunas Data e Hora no menu Adicionar Coluna . Se a coluna não tiver um tipo de dados definido, essas opções ficarão esmaecidas.

Ocorre uma situação semelhante para os filtros específicos do tipo, pois eles são específicos para determinados tipos de dados. Se sua coluna não tiver o tipo de dados correto definido, esses filtros específicos de tipo não estão disponíveis.

É crucial que você sempre trabalhe com os tipos de dados corretos para suas colunas. Quando você trabalha com fontes de dados estruturadas, como bancos de dados, as informações de tipo de dados são trazidas do esquema de tabela encontrado no banco de dados. Mas para fontes de dados não estruturadas, como arquivos TXT e CSV, é importante que você defina os tipos de dados corretos para as colunas provenientes dessa fonte de dados. Por padrão, o Power Query oferece uma detecção automática de tipo de dados para fontes de dados não estruturadas. Você pode ler mais sobre esse recurso e como ele pode ajudá-lo nos tipos de dados.

Analise e explore seus dados

Antes de preparar seus dados e adicionar etapas de transformação, habilite as ferramentas de criação de perfil de dados Power Query para descobrir informações sobre seus dados.

Power Query fornece três ferramentas de criação de perfil de dados:

Você também pode interagir com esses recursos, o que ajuda você a preparar seus dados.

Documentar seu trabalho

Documente uma solução do Power Query dando às etapas, consultas e grupos nomes e descrições significativos. Esses detalhes tornam a finalidade de cada transformação mais fácil de entender e manter.

Embora o Power Query crie automaticamente um nome de etapa para você no painel de etapas aplicadas, você também pode renomear suas etapas ou adicionar uma descrição a qualquer uma delas.

Dividir consultas grandes em módulos

Divida uma consulta Power Query grande em consultas referenciadas menores para facilitar a compreensão e a manutenção de suas fases de transformação. Embora uma única consulta possa conter todas as transformações e cálculos necessários, uma consulta com muitas etapas é mais fácil de gerenciar quando uma consulta faz referência à próxima.

Por exemplo, a consulta a seguir tem nove etapas e inclui uma etapa Mesclar com a tabela Preços.

Você pode dividir essa consulta em duas etapas da tabela Mesclagem com Preços . Dessa forma, é mais fácil entender as etapas que foram aplicadas à consulta de vendas antes da mesclagem. Para realizar essa operação, clique com o botão direito na etapa Mesclar com a tabela de Preços e selecione a opção Extrair Anterior.

Em seguida, você será solicitado com uma caixa de diálogo para dar um nome à nova consulta. Esta etapa divide efetivamente sua consulta em duas consultas. Uma consulta tem todas as etapas antes da mesclagem. A outra consulta tem uma etapa inicial que faz referência à sua nova consulta, bem como a todas as etapas subsequentes que estavam presentes na consulta original, a partir da etapa Mesclar com Preços para baixo.

Você também pode usar o referenciamento de consultas como achar melhor. Mas é uma boa ideia manter suas consultas em um nível que não parece assustador à primeira vista com tantos passos.

Organizar consultas em grupos

Use grupos no painel de consultas para manter seu trabalho organizado.

A única finalidade dos grupos é ajudá-lo a manter seu trabalho organizado servindo como pastas para suas consultas. Você pode criar grupos dentro de grupos caso precise. Mover consultas entre grupos é tão fácil quanto arrastar e soltar.

Tente dar a seus grupos um nome significativo que faça sentido para você e seu caso.

Consultas à prova de futuro

Crie consultas para lidar com as alterações esperadas nos dados de origem para que as atualizações futuras continuem sendo bem-sucedidas. Power Query fornece transformações que tornam uma consulta resiliente quando as linhas, colunas ou valores em uma fonte de dados mudam.

Defina o escopo da consulta, incluindo o que ela deve fazer e o que ela deve considerar em termos de estrutura, layout, nomes de coluna, tipos de dados e qualquer outro componente relevante.

As transformações a seguir podem ajudar uma consulta a permanecer resiliente às alterações:

Usar parâmetros

Use parâmetros do Power Query para armazenar e gerenciar valores que você pode reutilizar em transformações, funções de origem de dados e funções personalizadas. Os parâmetros facilitam a atualização de consultas porque você pode alterar um valor em um local em vez de editar cada consulta que a usa. Dois cenários comuns são:

• Argumento de passo: use um parâmetro como argumento de várias transformações acionadas pela interface do usuário.

  

• Argumento de função personalizada: Crie uma nova função a partir de uma consulta e referencie os parâmetros como os argumentos da sua função personalizada.

  

Os principais benefícios de criar e usar parâmetros são:

• Exibição centralizada de todos os parâmetros por meio da janela Gerenciar Parâmetros .

  

• Reutilização do parâmetro em várias etapas ou consultas.

• Torna a criação de funções personalizadas simples e fáceis.

Você pode até mesmo usar parâmetros em alguns dos argumentos dos conectores de dados. Por exemplo, você pode criar um parâmetro para o nome do servidor ao se conectar ao banco de dados do SQL Server. Em seguida, você pode usar esse parâmetro dentro da caixa de diálogo do banco de dados do SQL Server.

Se você alterar o local do servidor, tudo o que precisa fazer é atualizar o parâmetro para o nome do servidor e suas consultas serão atualizadas.

Criar funções reutilizáveis

Crie um Power Query função personalizada quando precisar aplicar o mesmo conjunto de transformações a consultas ou valores diferentes. Uma função personalizada Power Query mapeia um conjunto de valores de entrada para um único valor de saída e é criada a partir de operadores e funções de linguagem de fórmula M Power Query nativas.

Por exemplo, digamos que você tenha várias consultas ou valores que exigem o mesmo conjunto de transformações. Você pode criar uma função personalizada para invocar posteriormente nas consultas ou nos valores que você escolher. Essa função personalizada economiza tempo e ajuda você a gerenciar seu conjunto de transformações em um local central, que você pode modificar a qualquer momento.

As funções personalizadas do Power Query podem ser criadas a partir de consultas e parâmetros existentes. Por exemplo, imagine uma consulta que tenha vários códigos como uma cadeia de caracteres de texto e você queira criar uma função que decodifica esses valores.

Você começa tendo um parâmetro com um valor que serve como exemplo.

Nesse parâmetro, você cria uma nova consulta em que aplica as transformações necessárias. Nesse caso, você deseja dividir o código PTY-CM1090-LAX em vários componentes:

• Origem = PTY
• Destino = LAX
• Companhia Aérea = CM
• FlightID = 1090

Em seguida, você pode transformar essa consulta em uma função clicando com o botão direito do mouse na consulta e selecionando Criar Função. Por fim, você pode invocar sua função personalizada em qualquer uma de suas consultas ou valores.

Depois de mais algumas transformações, você pode ver que alcançou o resultado desejado e aplicou a lógica para essa transformação a partir de uma função personalizada.