Partilhar via

Agrupar ou resumir linhas

  • Artigo

No Power Query, pode agrupar valores em várias linhas num único valor agrupando as linhas de acordo com os valores numa ou mais colunas. Você pode escolher entre dois tipos de operações de agrupamento:

  • Agrupamentos de colunas.

  • Agrupamentos de linhas.

Para este tutorial, você está usando a tabela de exemplo a seguir.

Exemplo de tabela inicial.

Onde encontrar o botão Agrupar por

Pode encontrar o botão Agrupar por em três locais:

  • Na guia Página Inicial, no grupo Transformar.

    Captura de ecrã do friso do Power Query com a opção Agrupar por realçada no separador Base.

  • Na guia Transformar, no grupo Tabela.

    Captura de ecrã do friso do Power Query com a opção Agrupar por realçada no separador Transformar.

  • No menu de atalho, quando você clica com o botão direito do mouse para selecionar colunas.

    Captura de tela de uma tabela com a opção Grupo por enfatizada no menu de atalho.

Usar uma função de agregação para agrupar por uma ou mais colunas

Neste exemplo, seu objetivo é resumir o total de unidades vendidas no nível do país e do canal de vendas. Use as colunas País e Canal de Vendas para executar o grupo por operação.

  1. Selecione Agrupar por na guia Página Inicial .
  2. Selecione a opção Avançado , para que você possa selecionar várias colunas para agrupar.
  3. Selecione a coluna País .
  4. Selecione Adicionar agrupamento.
  5. Selecione a coluna Canal de vendas.
  6. Em Nome da nova coluna, insira Total de unidades, em Operação, selecione Soma e, em Coluna, selecione Unidades.
  7. Selecione OK

Captura de ecrã da caixa de diálogo Agrupar por com as colunas agregadas preenchidas.

Esta operação fornece a tabela a seguir.

Captura de tela da tabela de saída de exemplo com as colunas País, Canal de vendas e Total de unidades.

Operações disponíveis

Com o recurso Agrupar por , as operações disponíveis podem ser categorizadas de duas maneiras:

  • Operação de nível de linha
  • Operação ao nível da coluna

A tabela a seguir descreve cada uma dessas operações.

Nome da Operação Categoria Description
Sum Operação da coluna Soma todos os valores de uma coluna
Média Operação da coluna Calcula o valor médio de uma coluna
Mediana Operação da coluna Calcula a mediana a partir de uma coluna
Min Operação da coluna Calcula o valor mínimo de uma coluna
Máx. Operação da coluna Calcula o valor máximo de uma coluna
Percentil Operação da coluna Calcula o percentil, usando um valor de entrada de 0 a 100, de uma coluna
Contar valores distintos Operação da coluna Calcula o número de valores distintos de uma coluna
Contar linhas Operação de linha Calcula o número total de linhas de um determinado grupo
Contar linhas distintas Operação de linha Calcula o número de linhas distintas de um determinado grupo
Todas as linhas Operação de linha Produz todas as linhas agrupadas em um valor de tabela sem agregações

Nota

As operações Contar valores distintos e Percentil só estão disponíveis no Power Query Online.

Executar uma operação para agrupar por uma ou mais colunas

A partir do exemplo original, neste exemplo você cria uma coluna contendo o total de unidades e duas outras colunas que fornecem o nome e as unidades vendidas para o produto de melhor desempenho, resumidas no nível do país e do canal de vendas.

Captura de tela da tabela de saída de exemplo com operações.

  1. Use as seguintes colunas como Agrupar por colunas:

    • País
    • Canal de Vendas

  2. Crie duas novas colunas seguindo estas etapas:

    1. Agregue a coluna Unidades usando a operação Soma . Nomeie esta coluna Unidades totais.
    2. Adicione uma nova coluna Produtos usando a operação Todas as linhas .

    Captura de ecrã da caixa de diálogo Agrupar por com uma coluna não agregada.

Após a conclusão dessa operação, observe como a coluna Produtos tem valores [Tabela] dentro de cada célula. Cada valor [Tabela] contém todas as linhas que foram agrupadas pelas colunas País e Canal de Vendas da tabela original. Você pode selecionar o espaço em branco dentro da célula para ver uma visualização do conteúdo da tabela na parte inferior da caixa de diálogo.

Captura de ecrã do painel de pré-visualização de detalhes da tabela.

Nota

O painel de visualização de detalhes pode não mostrar todas as linhas que foram usadas para a operação de grupo por. Você pode selecionar o valor [Tabela] para ver todas as linhas pertencentes à operação correspondente grupo por.

Em seguida, você precisa extrair a linha que tem o valor mais alto na coluna Unidades das tabelas dentro da nova coluna Produtos e chamar essa nova coluna de produto de melhor desempenho.

Extraia as informações do produto com melhor desempenho

Com a nova coluna Produtos com valores [Tabela], você cria uma nova coluna personalizada acessando a guia Adicionar Coluna na faixa de opções e selecionando Coluna personalizada no grupo Geral .

Captura de ecrã do friso do Power Query com a opção Coluna personalizada realçada no separador Adicionar coluna.

Nomeie sua nova coluna como produto com melhor desempenho. Insira a fórmula Table.Max([Products], "Units" ) em Fórmula de coluna personalizada.

Captura de ecrã da caixa de diálogo Coluna personalizada com a fórmula para Table.Max introduzida.

O resultado dessa fórmula cria uma nova coluna com valores [Record]. Esses valores de registro são essencialmente uma tabela com apenas uma linha. Esses registros contêm a linha com o valor máximo para a coluna Unidades de cada valor [Tabela] na coluna Produtos .

Captura de ecrã do resultado da fórmula de coluna personalizada com Table.Max.

Com esta nova coluna de produto com melhor desempenho que contém valores [Record], pode selecionar o ícone de expansão, selecionar os campos Produto e Unidades e, em seguida, selecionar OK.

Captura de tela da operação de expansão para o valor de registro na coluna Produto com melhor desempenho.

Depois de remover a coluna Produtos e definir o tipo de dados para ambas as colunas recém-expandidas, o resultado será semelhante à imagem a seguir.

Captura de tela da tabela final com todas as transformações.

Agrupamento difuso

Nota

A seguinte funcionalidade só está disponível no Power Query Online.

Para demonstrar como fazer o "agrupamento difuso", considere a tabela de exemplo mostrada na imagem a seguir.

Screenshot de uma tabela com nove linhas de entradas que contêm várias grafias e maiúsculas do nome Miguel e William.

O objetivo do agrupamento difuso é fazer uma operação grupo por grupo que usa um algoritmo de correspondência aproximada para cadeias de texto. O Power Query utiliza o algoritmo de semelhança Jaccard para medir a semelhança entre pares de instâncias. Em seguida, ele aplica clustering hierárquico aglomerativo para agrupar instâncias. A imagem a seguir mostra a saída esperada, onde a tabela é agrupada pela coluna Pessoa .

Captura de tela de uma tabela mostrando entradas para Pessoa como Miguel e Mike e Frequência como 3 e 2, respectivamente.

Para fazer o agrupamento difuso, execute as mesmas etapas descritas anteriormente neste artigo. A única diferença é que, desta vez, na caixa de diálogo Agrupar por , você marca a caixa de seleção Usar agrupamento difuso.

Captura de tela da caixa de seleção Agrupamento difuso enfatizada na caixa de diálogo Agrupar por.

Para cada grupo de linhas, o Power Query seleciona a instância mais frequente como a instância "canónica". Se ocorrerem várias instâncias com a mesma frequência, o Power Query selecionará a primeira. Depois de selecionar OK na caixa de diálogo Agrupar por , você obterá o resultado esperado.

Captura de tela da tabela final de exemplo de agrupamento Fuzzy, sem tabela de transformação.

No entanto, você tem mais controle sobre a operação de agrupamento difuso expandindo as opções de grupo difuso.

Captura de tela do diálogo Grupo por diálogo com as opções de grupo Fuzzy enfatizadas.

As seguintes opções estão disponíveis para agrupamento difuso:

  • Limiar de semelhança (opcional): Esta opção indica como dois valores semelhantes devem ser agrupados. A configuração mínima de zero (0) faz com que todos os valores sejam agrupados. A configuração máxima de 1 só permite que os valores que correspondem exatamente sejam agrupados. O padrão é 0.8.
  • Ignorar maiúsculas e minúsculas: Quando as cadeias de texto são comparadas, maiúsculas e minúsculas são ignoradas. Por predefinição, esta opção encontra-se ativada.
  • Agrupar combinando partes de texto: o algoritmo tenta combinar partes de texto (como combinar Micro e soft na Microsoft) para agrupar valores.
  • Mostrar pontuações de similaridade: Mostrar pontuações de semelhança entre os valores de entrada e os valores representativos computados após o agrupamento difuso. Requer a adição de uma operação como Todas as linhas para mostrar essas informações em um nível linha por linha.
  • Tabela de transformação (opcional): você pode selecionar uma tabela de transformação que mapeie valores (como mapeamento de MSFT para a Microsoft) para agrupá-los.

Neste exemplo, uma tabela de transformação é usada para demonstrar como os valores podem ser mapeados. A tabela de transformação tem duas colunas:

  • De: A cadeia de texto a procurar na tabela.
  • Para: A cadeia de caracteres de texto a ser usada para substituir a cadeia de texto na coluna De .

A imagem a seguir mostra a tabela de transformação usada neste exemplo.

Captura de tela da tabela mostrando De valores de Mike e William, e Para valores de Miguel e Bill.

Importante

É importante que a tabela de transformação tenha as mesmas colunas e nomes de colunas mostrados na imagem anterior (eles devem ser rotulados como "De" e "Para"). Caso contrário, o Power Query não reconhece a tabela como uma tabela de transformação.

Retorne à caixa de diálogo Agrupar por, expanda Opções de grupo difuso, altere a operação de Contar linhas para Todas as linhas, habilite a opção Mostrar pontuações de semelhança e selecione o menu suspenso Tabela de transformação.

Captura de tela do menu suspenso da tabela de transformação de exemplo de agrupamento difuso.

Depois de selecionar a tabela de transformação, selecione OK. O resultado dessa operação fornece as seguintes informações:

Captura de tela da tabela final de amostra de agrupamento difuso com tabela de transformação.

Neste exemplo, a opção Ignorar maiúsculas e minúsculas foi habilitada, portanto, os valores na coluna De da tabela Transformação são usados para procurar a cadeia de texto sem considerar o caso da cadeia de caracteres. Essa operação de transformação ocorre primeiro e, em seguida, a operação de agrupamento difuso é executada.

A pontuação de similaridade também é mostrada no valor da tabela ao lado da coluna pessoa, que reflete exatamente como os valores foram agrupados e seus respetivos escores de similaridade. Você pode expandir esta coluna, se necessário, ou usar os valores das novas colunas Frequência para outros tipos de transformações.

Nota

Ao agrupar por várias colunas, a tabela de transformação executa a operação de substituição em todas as colunas se a substituição do valor aumentar a pontuação de similaridade.

Para obter mais informações sobre como as tabelas de transformação funcionam, vá para Preceitos da tabela de transformação.

Consulte também

Adicionar uma coluna personalizada
Remover duplicados