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 Wrangling de dados em Power Query.

Escolha 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, até bancos de dado, como Microsoft SQL Server, e serviços de SaaS populares, como o Microsoft Dynamics 365 e o Salesforce. Se você não vir sua fonte de dados listada na janela obter dados , você sempre poderá usar o conector ODBC ou OLEDB para se conectar à fonte de dados.

Usar o melhor conector para a tarefa fornecerá a melhor experiência e desempenho. por exemplo, usar o conector de 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 obtenção de dados muito melhor, mas o conector de SQL Server também oferece recursos que podem melhorar sua experiência e desempenho, como o dobramento de consulta. Para ler mais sobre o dobramento de consulta, consulte Power Query o dobramento de consulta.

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 Data Preview. Neste estágio, você receberá uma janela amigável para selecionar os dados que deseja obter da fonte de dados, se o conector permitir, e uma simples visualização de dados desses dados. Você pode até mesmo selecionar vários conjuntos de dados de sua fonte de dado por meio da janela navegador , conforme mostrado na imagem a seguir.

Janela Navegador de exemplo.

Observação

Para ver a lista completa de conectores disponíveis no Power Query, consulte conectores em Power Query.

Filtrar antecipadamente

É sempre recomendável filtrar os dados nos estágios iniciais de sua consulta ou o mais cedo possível. Alguns conectores aproveitarão seus filtros por meio do dobramento de consulta, conforme descrito em Power Query dobramento de consulta. 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 em mãos mostrando apenas os dados relevantes na seção 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 você deseja manter ou filtrar. Você também pode usar a barra de pesquisa para ajudá-lo a encontrar os valores em sua coluna.

Menu de filtro automático no Power Query.

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

Digite um filtro específico para uma coluna de data.

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

Está no filtro específico de data anterior.

Observação

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

Usar os tipos de dados corretos

Alguns recursos do 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 estarão disponíveis. Mas se a coluna não tiver um tipo de dados definido, essas opções serão esmaecidas.

Opção de tipo específico no menu Adicionar coluna.

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

Digite um filtro específico para uma coluna de data.

É crucial que você sempre trabalhe com os tipos de dados corretos para suas colunas. Ao trabalhar com fontes de dados estruturadas, como bancos de dado, 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 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 estruturados. Você pode ler 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, consulte 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 Power Query para descobrir facilmente informações sobre seus dados.

Ferramentas de visualização de dados ou criação de perfil de dados no Power Query.

Essas ferramentas de criação de perfil de dados ajudam a entender melhor seus dados. As ferramentas fornecem visualizações pequenas que mostram informações sobre uma base 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 que demonstram a frequência e a distribuição dos valores em cada uma das colunas.
  • Perfil — de coluna Fornece uma exibição mais completa de sua coluna e as estatísticas associadas a ela.

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

Opções de foco de qualidade de dados.

Observação

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

Documente seu trabalho

É recomendável que você documente suas consultas renomeando ou adicionando uma descrição às suas etapas, consultas ou grupos, como você pode ver.

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

Painel etapas aplicadas com etapas documentadas e descrição adicionadas.

Observação

Para saber mais sobre todos os recursos e componentes disponíveis encontrados no painel etapas aplicadas, consulte usando a lista etapas aplicadas.

Adote uma abordagem modular

É totalmente possível criar uma única consulta que contenha todas as transformações e cálculos que você possa precisar. Mas se a consulta contiver um grande número de etapas, poderá ser uma boa ideia dividir a consulta em várias consultas, em que uma consulta referencia a 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.

Painel etapas aplicadas com etapas documentadas e descrição adicionadas.

Você pode dividir essa consulta em duas na etapa mesclar com a tabela preços . Dessa forma, é mais fácil entender as etapas que foram aplicadas à consulta de vendas antes da mesclagem. Para executar essa operação, clique com o botão direito do mouse na etapa mesclar com a tabela preços e selecione a opção extrair anterior .

Extrair etapa anterior.

Em seguida, você será avisado com uma caixa de diálogo para dar um nome à sua nova consulta. Isso irá dividir efetivamente a 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 em sua consulta original na etapa mesclar com preços para baixo.

Consulta original após a ação extrair etapa anterior.

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

Observação

Para saber mais sobre referências de consulta, consulte noções básicas sobre o painel consultas.

Criar grupos

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

Trabalhando com grupos no Power Query.

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 no painel consultas, consulte noções básicas sobre o painel consultas.

Consultas de verificação futura

A criação de uma consulta que não terá problemas durante uma atualização futura é uma prioridade mais alta. Há vários recursos no Power Query para tornar sua consulta resiliente a alterações e ser capaz de atualizar mesmo quando alguns componentes da fonte de dados forem alterados.

É uma prática recomendada definir o escopo da consulta quanto ao que deve ser feito e o que deve ser considerado 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 às alterações são:

  • Se sua 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, consulte filtrar uma tabela por posição de linha.

  • Se sua consulta tiver um número dinâmico de colunas, mas você só precisa selecionar colunas específicas do conjunto de informações, você poderá usar o recurso escolher colunas .

    Observação

    Para saber mais sobre como escolher ou remover colunas, consulte Choose ou remove Columns.

  • Se sua consulta tiver um número dinâmico de colunas e você precisar desdinamizar apenas um subconjunto de suas colunas, você poderá usar o recurso somente colunas selecionadas como UNPIVOT .

    Observação

    Para saber mais sobre as opções para desdinamizar suas colunas, consulte colunas não dinâmicas.

  • Se a consulta tiver uma etapa que altere o tipo de dados de uma coluna, mas algumas células gerarem erros, já que os valores não estão de acordo 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, consulte lidando com erros.

Usar parâmetros

A criação de consultas que são dinâmicas e flexíveis é uma prática recomendada. Os parâmetros no Power Query ajudam 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 Step — Você pode usar um parâmetro como o argumento de várias transformações controladas a partir da interface do usuário.

    Selecione o parâmetro para o argumento de transformação.

  • Argumento de função personalizada — Você pode criar uma nova função de uma consulta e parâmetros de referência como os argumentos de sua função personalizada.

    Criar função.

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

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

    Janela Gerenciar parâmetros.

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

  • Torna a criação de funções personalizadas direta 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 seu banco de dados do SQL Server. em seguida, você poderia usar esse parâmetro dentro da caixa de diálogo SQL Server banco de dados.

SQL Server caixa de diálogo de banco de dados com parâmetro para o nome do servidor.

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.

Observação

Para saber mais sobre como criar e usar parâmetros, consulte usando parâmetros.

Criar funções reutilizáveis

Se você se deparar com uma situação em que precisa aplicar o mesmo conjunto de transformações a diferentes consultas ou valores, criar uma Power Query função personalizada que possa ser reutilizada quantas vezes você precisar pode ser benéfico. Uma Power Query função personalizada é um mapeamento de um conjunto de valores de entrada para um único valor de saída e é criado a partir de funções e operadores nativos M.

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 pode ser chamada posteriormente em relação às consultas ou aos valores de sua escolha. Essa função personalizada economizará tempo e ajudará você a gerenciar seu conjunto de transformações em um local central, que pode ser modificado a qualquer momento.

Power Query funções personalizadas 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.

Lista de códigos.

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

Valor de código de parâmetro de exemplo.

A partir desse parâmetro, você cria uma nova consulta na qual aplica as transformações de que precisa. Para esse caso, você deseja dividir o código PTY-CM1090-LAX em vários componentes:

  • Origem = Pty
  • Destino = LAX
  • Companhia aérea = cm
  • Flightid = 1090

Exemplo de consulta de transformação.

Em seguida, você pode transformar essa consulta em uma função fazendo um clique 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, conforme mostrado na imagem a seguir.

Invocando uma função personalizada.

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

Consulta de saída final depois de invocar uma função personalizada.

Observação

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