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 fornece a melhor experiência e desempenho. Por exemplo, usar o conector do SQL Server em vez do conector ODBC ao ligar-se a uma base de dados do SQL Server não só proporciona uma melhor experiência de obtenção de Dados, como o conector do SQL Server também oferece funcionalidades que podem melhorar a sua experiência e desempenho, como a otimização de consultas (query folding). 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 .

Filtro antecipado

Recomendamos sempre que filtre os seus dados nas fases iniciais da sua consulta ou o mais cedo possível. Alguns conectores tiram partido dos seus filtros através do encapsulamento de consultas, tal como descrito em Descrição geral da avaliação de consultas e encapsulamento de consultas no Power Query. Também é uma prática recomendada filtrar quaisquer dados que não sejam relevantes para o seu caso. Essa filtragem permite que você se concentre melhor na 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 recupera dados que estão no número anterior x de segundos, minutos, horas, dias, semanas, meses, trimestres ou anos.

Fazer operações caras por último

Certas operações requerem 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 qualquer resultado. Em vez disso, eles operam sobre os dados em um modo chamado de "streaming". Os dados "fluem" continuamente, e os resultados são devolvidos durante o 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 nessa ordem ajuda a minimizar a quantidade de tempo que você gasta aguardando a renderização da visualização cada vez que você adiciona uma nova etapa à sua consulta.

Trabalhar temporariamente com 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 de "Manter Primeiras Linhas" e limitar o número de linhas com 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 são utilizá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. 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 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 uma 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 ajuda 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 esta consulta em duas na etapa Mesclar com tabela de Preços. Dessa forma, fica mais fácil entender as etapas que foram aplicadas à consulta de vendas antes da mesclagem. Para fazer esta operação, clique com o botão direito do rato na etapa Mesclar com Preços da tabela e selecione a opção Extrair Anterior.

Em seguida, será exibida 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 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 usar o referenciamento 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 é fazendo 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 simples quanto arrastar e soltar.

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

Consultas à prova de 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 .

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

  Observação

  Para saber mais sobre as opções para despivotar as 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.

  Observação

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

Parâmetros de uso

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 são atualizadas.

Criar funções reutilizáveis

Você pode se encontrar em uma situação em que precisa aplicar o mesmo conjunto de transformações a diferentes consultas ou valores. Nesse caso, criar uma função personalizada do Power Query que possa ser reutilizada quantas vezes forem necessárias 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. Essa função personalizada economizaria tempo e ajudaria 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 tem vários códigos como uma cadeia de texto e você deseja criar uma função que decodifica esses valores.

Você começa tendo um parâmetro com 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.

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