Práticas recomendadas ao trabalhar com 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.

Escolher o conector correto

O Power Query oferece um grande número de conectores de dados. Esses conectores variam de fontes de dados, como arquivos TXT, CSV e Excel, a 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.

O uso do melhor conector para a tarefa fornecerá a você a melhor experiência e o melhor desempenho. Por exemplo, o uso do conector do SQL Server em vez do conector ODBC ao se conectar a um banco de dados SQL Server não apenas fornece uma experiência de Obter Dados muito melhor, mas o conector do SQL Server também oferece recursos que podem aprimorar sua experiência e seu desempenho, como a dobragem de consultas. Para ler mais sobre a dobragem de consultas, acesse Visão geral da avaliação e da dobragem de consultas no Power Query.

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

Observação

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

Filtrar antecipadamente

É sempre recomendável filtrar seus dados nos estágios iniciais da consulta ou o mais cedo possível. Alguns conectores aproveitarão seus filtros por meio da dobragem de consultas, conforme descrito em Visão geral da avaliação e da dobragem 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. Isso permitirá que você se concentre melhor em sua tarefa, 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 na coluna para selecionar os valores que deseja manter ou filtrar. Você também pode usar a barra de pesquisa para ajudar a encontrar os valores na coluna.

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

Esses filtros específicos de tipo podem ajudar você a criar um filtro dinâmico que sempre recuperará dados que estão no x números de segundos, minutos, horas, dias, semanas, meses, trimestres ou anos anteriores, conforme mostrado na imagem a seguir.

Observação

Para saber mais sobre como filtrar dados com base em valores de uma coluna, acesse Filtrar por valores.

Fazer operações caras por último

Determinadas operações exigem a leitura completa da fonte de dados para retornar qualquer resultado e, portanto, serão lentas para visualização 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 algum resultado, a operação de classificação deverá primeiro ler todas as linhas.

Outras operações (como filtros) não precisam ler todos os dados para retornar resultados. Em vez disso, elas operam sobre os dados no que é chamado de uma forma de "fluxo". Os dados "fluem" e os resultados são retornados ao longo do caminho. No Editor do Power Query, essas operações só precisam ler o quanto baste dos dados de origem para preencher a visualização.

Sempre que possível, execute essas operações de fluxo primeiro e faça operações mais caras por último. Isso ajudará a minimizar o tempo gasto aguardando a visualização ser renderizada cada vez que você adicionar uma nova etapa à consulta.

Trabalhar temporariamente em um subconjunto de 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. Em seguida, depois de adicionar todas as etapas necessárias, remova a etapa "Manter Primeiras Linhas".

Usar os tipos de dados corretos

Alguns recursos em Power Query são relativos ao contexto do tipo de dados da coluna selecionada. Por exemplo, ao selecionar uma coluna de data, as opções disponíveis no grupo de Colunas de data e hora no menu Adicionar Coluna ficarão disponíveis. Mas se a coluna não tiver um tipo definido de dados, essas opções ficarão esmaecidas.

Ocorre uma situação semelhante para os filtros específicos de 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 ficarão disponíveis.

É crucial que você sempre trabalhe com os tipos de dados corretos para suas colunas. Ao trabalhar com fontes de dados estruturadas, como bancos de dados, as informações de tipo de dados serão trazidas do esquema da tabela encontrado no banco de dados. Mas para fontes de dados não estruturadas, como arquivos TXT e CSV, é importante definir os tipos de dados corretos para as colunas provenientes dessas fontes de dados. Por padrão, Power Query oferece uma detecção automática de tipo de dados para fontes de dados não estruturadas. Leia mais sobre esse recurso e como ele pode ajudá-lo em Tipos de Dados.

Observação

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

Explorar seus dados

Antes de começar a preparar os 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 informações sobre seus dados com facilidade.

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

• Qualidade da coluna – Fornece um pequeno gráfico de barras e três indicadores com a representação de quantos valores na coluna se enquadram nas categorias de valores válidos, de erro ou vazios.
• Distribuição de coluna – Fornece um conjunto de visuais abaixo dos nomes das colunas, os quais demostram a frequência e a distribuição dos valores em cada uma das colunas.
• Perfil da 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 ajudará você a preparar seus dados.

Observação

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

Documentar seu trabalho

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

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

Observação

Para saber mais sobre todos os recursos e componentes disponíveis incluídos no painel de etapas aplicadas, acesse Usar a lista Etapas aplicadas.

Adotar uma abordagem modular

É totalmente possível criar apenas uma 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 faça referência à próxima. O objetivo dessa abordagem é simplificar e desacoplar as fases de transformação em partes menores para que sejam mais fáceis de entender.

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

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

Em seguida, você verá uma caixa de diálogo para dar um nome à nova consulta. Isso dividirá efetivamente sua consulta em duas consultas. Uma consulta terá todas as consultas antes da mesclagem. A outra consulta terá uma etapa inicial que fará referência à nova consulta e ao restante das etapas que você tinha em sua consulta original da etapa Mesclar com a tabela de Preços em diante.

Você também pode aproveitar o uso de referenciamento de consulta conforme achar melhor. Mas é uma boa ideia manter as consultas em um nível que não pareça assustador à primeira vista com tantos passos.

Observação

Para saber mais sobre a referência de consulta, acesse Reconhecimento do painel de consultas.

Criar grupos

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

A única finalidade dos grupos é ajudar você 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 nomes significativos que façam sentido para você e seu caso.

Observação

Para saber mais sobre todos os recursos e componentes disponíveis no painel de consultas, acesse Reconhecimento do painel de consultas.

Consultas resilientes

Ter a certeza de criar uma consulta que não tenha problemas durante uma atualização futura é uma prioridade máxima. Há vários recursos no Power Query para tornar sua consulta resiliente a alterações e capaz de ser atualizada mesmo quando alguns componentes da sua fonte de dados forem 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 colunas, tipos de dados e qualquer outro componente que você considere relevante para o escopo.

Alguns exemplos de transformações que podem ajudar você a tornar sua consulta resiliente a alterações são:

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

  Observação

  Para saber mais sobre como filtrar dados por posição de linha, acesse Filtrar uma tabela por posição de linha.

• Se a consulta tiver um número dinâmico de colunas, mas você apenas 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, acesse Escolher ou remover colunas.

• Se a consulta tiver um número dinâmico de colunas e você precisar transformar colunas em linhas apenas em um subconjunto das colunas, você poderá usar o recurso de transformar colunas selecionadas em linhas.

  Observação

  Para saber mais sobre as opções para transformar colunas em linhas, acesse Transformar colunas em linhas.

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

  Observação

  Para saber mais sobre como trabalhar e lidar com erros, acesse Como 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 forma de armazenar e gerenciar facilmente um valor que possa 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 orientadas pela interface do usuário.

  

• Argumento de função personalizada – Você pode criar uma função com base em uma consulta e referenciar parâmetros como os 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ácil.

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 precisará fazer será 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 Como usar parâmetros.

Criar funções reutilizáveis

Se você se deparar com uma situação em que precise aplicar o mesmo conjunto de transformações a diferentes consultas ou valores, a criação de uma função personalizada do Power Query que possa ser reutilizada quantas vezes precisar poderá ser útil. Uma função personalizada do Power Query é um mapeamento de um conjunto de valores de entrada em um único valor de saída e é criada com base em operadores e funções de M nativas.

Por exemplo, digamos que você tenha várias consultas ou valores que exigem o mesmo conjunto de transformações. Você poderia criar uma função personalizada que posteriormente pudesse ser invocada nas consultas ou nos 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 com base em 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ê deseja criar uma função que decodificará esses valores.

Você começa com um parâmetro que tenha um valor que sirva de exemplo.

Com base nesse parâmetro, você cria outra consulta em que aplica as transformações necessárias. Para esse caso, você deseja dividir o código PTY-CM1090-LAX em vários componentes:

• Origem = PTY
• Destino = LAX
• Airline = 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 a função personalizada em qualquer uma de suas consultas ou seus valores, conforme mostrado na imagem a seguir.

Depois de mais algumas transformações, você poderá ver que alcançou a saída desejada e aproveitou a lógica dessa transformação de uma função personalizada.

Observação

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