Práticas recomendadas ao trabalhar com o Power Query

Este artigo contém algumas sugestões e truques para tirar o máximo partido da sua experiência de disputa de dados no Power Query.

Escolha o conector certo

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 Microsoft Dynamics 365 e 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 irá fornecer-lhe 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 apenas fornece uma experiência de obtenção de dados muito melhor, mas o conector do SQL Server também oferece recursos que podem melhorar sua experiência e desempenho, como dobragem de consulta. Para ler mais sobre dobragem de consultas, aceda a Descrição geral da avaliação e dobragem de consultas no Power Query.

Cada conector de dados segue uma experiência padrão, conforme explicado em Obtendo dados. Essa experiência padronizada tem um estágio chamado Data Preview. Nesta etapa, você recebe uma janela amigável para selecionar os dados que deseja obter de sua 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 sua fonte de dados através da janela Navegador , conforme mostrado na imagem a seguir.

Filtre cedo

É sempre recomendável filtrar seus dados nos estágios iniciais da consulta ou o mais cedo possível. Alguns conectores tirarão partido dos seus filtros através da dobragem de consultas, conforme descrito em Descrição geral da avaliação de consultas e dobragem de consultas no Power Query. Também é uma prática recomendada filtrar quaisquer dados que não sejam relevantes para o seu caso. Isso permitirá 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 do tipo, como No anterior para uma coluna de data, datetime ou até mesmo fuso horário de data.

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

Fazer operações caras por último

Algumas operações requerem a leitura da fonte de dados completa para retornar quaisquer resultados 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 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 quaisquer resultados. Em vez disso, eles operam sobre os dados no que é chamado de "streaming". 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 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. Isso ajudará a minimizar a quantidade de tempo que você gasta aguardando a visualização renderizar cada vez que você adiciona uma nova etapa à sua consulta.

Trabalhar temporariamente contra um subconjunto dos seus dados

Se a adição de novos passos à sua consulta no Editor do Power Query for lenta, considere primeiro efetuar uma operação "Manter Primeiras Linhas" e limitar o número de linhas contra as quais está a trabalhar. Em seguida, depois de adicionar todas as etapas necessárias, remova a etapa "Manter primeiras linhas".

Use os tipos de dados corretos

Algumas funcionalidades do Power Query são contextuais ao 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 estarão disponíveis. Mas se a coluna não tiver um conjunto de tipos de dados, essas opções ficarão acinzentadas.

Uma situação semelhante ocorre para os filtros específicos do tipo, uma vez que 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 do tipo não estarão disponíveis.

É crucial que você sempre trabalhe com os tipos de dados corretos para suas colunas. Ao trabalhar com fontes de dados estruturados, como bancos de dados, as informações de tipo de dados serã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 definir os tipos de dados corretos para as colunas provenientes dessa fonte de dados. Por predefinição, o Power Query oferece uma deteção automática de tipos de dados para origens de dados não estruturadas. Você pode ler mais sobre esse recurso e como ele pode ajudá-lo em Tipos de dados.

Explore os seus dados

Antes de começar a preparar os seus dados e a adicionar novos passos de transformação, recomendamos que ative as ferramentas de criação de perfil de dados do Power Query para descobrir facilmente informações sobre os seus dados.

Estas ferramentas de definição de perfis de dados ajudam-no a compreender melhor os 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 colunas—Fornece um conjunto de elementos visuais abaixo dos nomes das colunas que mostram a frequência e a distribuição dos valores em cada uma das colunas.
• Perfil da coluna—Fornece uma visão mais completa da sua coluna e das estatísticas associadas a ela.

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

Documente o seu trabalho

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

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.

Adote 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 dividi-la em várias consultas, onde uma consulta faz referência à próxima. O objetivo dessa abordagem é simplificar e separar 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ê pode dividir essa consulta em duas na etapa da tabela Mesclar com Preços. Dessa forma, fica 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 da tabela Mesclar com preços e selecione a opção Extrair anterior.

Em seguida, você será solicitado com uma caixa de diálogo para dar um nome à sua nova consulta. Isso efetivamente dividirá 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 à sua nova consulta e o restante das etapas que você tinha na consulta original da etapa da tabela Mesclar com Preços para baixo.

Você também pode aproveitar o uso da referência de consulta como achar melhor. Mas é uma boa ideia manter suas consultas em um nível que não pareça assustador à primeira vista com tantas etapas.

Criar grupos

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

O único objetivo dos grupos é ajudá-lo a manter seu trabalho organizado, servindo como pastas para suas consultas. Você pode criar grupos dentro de grupos, caso seja necessário. Mover consultas entre grupos é tão fácil quanto arrastar e soltar.

Tente dar aos seus grupos um nome significativo que faça sentido para si e para o seu caso.

Consultas preparadas para o futuro

Certificar-se de que você cria uma consulta que não terá problemas durante uma atualização futura é uma prioridade máxima. Existem várias funcionalidades no Power Query para tornar a sua consulta resiliente a alterações e capaz de atualizar mesmo quando alguns componentes da sua origem de dados são alterados.

É uma prática recomendada definir o escopo da sua consulta quanto ao que ela deve fazer e o que ela deve levar em conta 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 sua consulta tiver um número dinâmico de linhas com dados, mas um número fixo de linhas que servem como rodapé que deve ser removido, pode utilizar a funcionalidade Remover linhas inferiores .

  Nota

  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 .

  Nota

  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 despivotar apenas um subconjunto de colunas, poderá usar o recurso despivotar apenas colunas selecionadas.

  Nota

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

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

  Nota

  Para saber mais sobre como trabalhar e lidar com erros, vá para Lidando com erros.

Utilizar parâmetros

Criar consultas dinâmicas e flexíveis é uma prática recomendada. Os parâmetros no Power Query ajudam-no a tornar as 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 muitas maneiras diferentes. Mas é mais comumente usado em dois cenários:

• Argumento da etapa—Você pode usar um parâmetro como o argumento de várias transformações conduzidas a partir 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 os argumentos de sua função personalizada.

  

Os principais benefícios da criação e utilização de parâmetros são:

• Vista centralizada de todos os seus parâmetros através da janela Gerir 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é 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 você precisa fazer é atualizar o parâmetro para o nome do servidor e suas consultas serão atualizadas.

Criar funções reutilizáveis

Se você se encontrar em uma situação em que precisa aplicar o mesmo conjunto de transformações a consultas ou valores diferentes, criar uma função personalizada do Power Query que possa ser reutilizada quantas vezes precisar pode ser benéfico. 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 funções e operadores M nativos.

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 mais tarde pode ser invocada contra as consultas ou valores de sua escolha. Esta função personalizada poupa-lhe tempo e ajuda-o a gerir o seu conjunto de transformações numa localização central, que 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 decodificará esses valores.

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

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

• Origem = PTY
• Destino = LAX
• Companhia aérea = CM
• ID do voo = 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. Finalmente, você pode invocar sua função personalizada em qualquer uma de suas consultas ou valores, conforme mostrado na imagem a seguir.

Depois de mais algumas transformações, você pode ver que atingiu a saída desejada e aproveitou a lógica para tal transformação a partir de uma função personalizada.