Práticas recomendadas ao trabalhar com o Power Query

Este artigo contém algumas dicas e truques para aproveitar ao máximo sua experiência de estruturação de dados no Power Query.

Escolha o conector certo

O Power Query oferece um grande número de conectores de dados. Esses conectores vão desde fontes de dados, como arquivos TXT, CSV e Excel, até bancos de dados como o Microsoft SQL Server e serviços SaaS populares, como o Microsoft Dynamics 365 e o Salesforce. Se você não vir sua fonte de dados listada na janela Obter Dados , sempre poderá usar o conector ODBC ou OLEDB para se conectar à fonte de dados.

Usar o melhor conector para a tarefa fornece a melhor experiência e desempenho. Por exemplo, usar o conector do SQL Server em vez do conector ODBC ao se conectar a um banco de dados do SQL Server não só fornece uma melhor experiência Get Data, mas o conector do SQL Server também oferece recursos que podem melhorar sua experiência e desempenho, como o dobramento de consultas. Para ler mais sobre a dobragem de consultas, acesse Visão geral da avaliação da consulta e da dobragem de consultas no 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 .

Observação

Para ver a lista completa de conectores disponíveis no Power Query, acesse Conectores no Power Query.

Filtrar antecipadamente

Sempre recomendamos que você filtre seus dados nos estágios iniciais da consulta ou o mais cedo possível. Alguns conectores aproveitam-se de seus filtros por meio da mesclagem de consultas, conforme descrito em Visão geral da avaliação de consultas e da mesclagem de consultas no Power Query. Também é uma prática recomendada filtrar todos os dados que não são relevantes para o seu caso. Essa filtragem permite que você se concentre melhor em sua tarefa em mãos mostrando apenas os dados relevantes na seção de visualização de dados.

Você pode usar o menu de filtro automático que exibe uma lista distinta dos valores encontrados em sua coluna para selecionar os valores que deseja manter ou filtrar. Você também pode usar 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.

Screenshot de um filtro específico de tipo amostra para uma coluna de datas com a opção anterior enfatizada.

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.

Observação

Para saber mais sobre como filtrar seus dados com base em valores de uma coluna, vá para Filtrar por valores.

Fazer operações caras por último

Determinadas operações exigem a leitura da fonte de dados completa para retornar quaisquer resultados e, portanto, é lento para visualizar no editor do Power Query. Por exemplo, se você executar uma classificação, é possível que as primeiras linhas classificadas estejam no final dos dados de origem. Portanto, 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.

Trabalhe temporariamente em um subconjunto dos seus dados

Se a adição de novas etapas à consulta no editor do Power Query for lenta, considere primeiro fazer uma operação "Manter Primeiras Linhas" e limitar o número de linhas em que você está trabalhando. Depois de adicionar todas as etapas necessárias, remova a etapa "Manter Primeiras Linhas".

Usar os tipos de dados corretos

Alguns recursos no Power Query são contextuais para o tipo de dados da coluna selecionada. Por exemplo, ao selecionar uma coluna de data, as opções disponíveis no grupo de colunas Data e Hora no menu Adicionar Coluna são utilizáveis. Mas 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.

Observação

Para saber mais sobre a importância dos tipos de dados e como trabalhar com eles, acesse os tipos de dados.

Explore os seus dados

Antes de começar a preparar seus dados e adicionar novas etapas de transformação, recomendamos que você habilite as ferramentas de criação de perfil de dados do Power Query para descobrir facilmente informações sobre seus dados.

Essas ferramentas de criação de perfil de dados ajudam você a entender melhor seus dados. As ferramentas fornecem visualizações pequenas que mostram informações por coluna, como:

• Qualidade da coluna — Fornece um gráfico de barras pequeno e três indicadores com uma representação de quantos valores na coluna se enquadram nas categorias de valores válidos, de erros ou vazios.
• Distribuição de coluna — Fornece um conjunto de visuais abaixo dos nomes das colunas que mostram a frequência e a distribuição dos valores em cada uma das colunas.
• Perfil de coluna – Fornece uma exibição mais completa da coluna e das estatísticas associadas a ela.

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

Observação

Para saber mais sobre as ferramentas de criação de perfil de dados, acesse as ferramentas de criação de perfil de dados.

Documentar seu trabalho

Recomendamos que você documente suas consultas renomeando ou adicionando uma descrição às suas etapas, consultas ou grupos conforme quiser.

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.

Observação

Para saber mais sobre todos os recursos e componentes disponíveis encontrados dentro do painel de etapas aplicadas, acesse Usar a lista de etapas aplicadas.

Adotar uma abordagem modular

É totalmente possível criar uma única consulta que contenha todas as transformações e cálculos necessários. Mas se a consulta contiver um grande número de etapas, talvez seja uma boa ideia dividir a consulta em várias consultas, em que uma consulta faz referência à próxima. O objetivo dessa abordagem é simplificar e desacoplar as fases de transformação em partes menores para que elas sejam mais fáceis de entender.

Por exemplo, digamos que você tenha uma consulta com as nove etapas mostradas na imagem a seguir.

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 consultas 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 a referência de consulta conforme achar adequado. Mas é uma boa ideia manter suas consultas em um nível que não parece assustador à primeira vista com tantos passos.

Observação

Para saber mais sobre a referência à consulta, acesse Noções básicas sobre o painel de consultas.

Criar grupos

Uma ótima maneira de manter seu trabalho organizado é fazendo uso de grupos no painel de consultas.

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.

Observação

Para saber mais sobre todos os recursos e componentes disponíveis encontrados dentro do painel de consultas, acesse Noções básicas sobre o painel consultas.

Consultas à prova de futuro

Garantir que você crie uma consulta que não terá problemas durante uma atualização futura é uma prioridade máxima. Há vários recursos no Power Query para tornar sua consulta resiliente às alterações e capaz de atualizar mesmo quando alguns componentes da fonte de dados são alterados.

É uma prática recomendada definir o escopo da consulta quanto ao 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 que você considere relevante para o escopo.

Alguns exemplos de transformações que podem ajudá-lo a tornar sua consulta resiliente a alterações são:

• Se a consulta tiver um número dinâmico de linhas com dados, mas um número fixo de linhas que servem como o rodapé que deve ser removido, você poderá usar o recurso Remover linhas inferiores .

  Observação

  Para saber mais sobre como filtrar seus dados por posição de linha, vá para Filtrar uma tabela por posição de linha.

• Se a consulta tiver um número dinâmico de colunas, mas você só precisar selecionar colunas específicas do conjunto de dados, poderá usar o recurso Escolher colunas .

  Observação

  Para saber mais sobre como escolher ou remover colunas, vá para Escolher ou remover colunas.

• Se a consulta tiver um número dinâmico de colunas e você precisar desfazer pivot de apenas um subconjunto de suas colunas, você poderá usar o recurso de despivotar apenas colunas selecionadas.

  Observação

  Para saber mais sobre as opções para desagrupar suas colunas, vá para Desagrupar colunas.

• Se a consulta tiver uma etapa que altera o tipo de dados de uma coluna, mas algumas células produzem erros, pois os valores não estão em conformidade com o tipo de dados desejado, você pode remover as linhas que geraram valores de erro.

  Observação

  Para saber mais sobre como trabalhar e lidar com erros, acesse Lidar com erros.

Usar parâmetros

Criar consultas dinâmicas e flexíveis é uma prática recomendada. Os parâmetros no Power Query ajudam você a tornar suas consultas mais dinâmicas e flexíveis. Um parâmetro serve como uma maneira de armazenar e gerenciar facilmente um valor que pode ser reutilizado de várias maneiras diferentes. Mas é mais comumente usado em dois cenários:

• Argumento de etapa: você pode usar um parâmetro como o argumento de várias transformações controladas da interface do usuário.

  

• Argumento de função personalizada: você pode criar uma nova função a partir de uma consulta e referenciar parâmetros como argumentos de 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.

Observação

Para saber mais sobre como criar e usar parâmetros, acesse Usar parâmetros.

Criar funções reutilizáveis

Você pode se encontrar em uma situação em que precisa aplicar o mesmo conjunto de transformações a consultas ou valores diferentes. Nesse caso, a criação de uma função personalizada do Power Query que pode ser reutilizado quantas vezes você precisar pode ser benéfica. Uma função personalizada do Power Query é um mapeamento de um conjunto de valores de entrada para um único valor de saída e é criada a partir de operadores e funções M 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 que posteriormente pode ser invocada em relação às consultas ou valores de sua escolha. Essa função personalizada economizaria tempo e o ajudaria 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.

Observação

Para saber mais sobre como criar e usar funções personalizadas no Power Query no artigo Funções Personalizadas.