Agrupar ou resumir linhas
No Power Query, é possível agrupar valores de diversas linhas em um único valor. Basta agrupar as linhas de acordo com os valores em uma ou mais colunas. Existem dois tipos de operações de agrupamento para escolher:
Agrupamentos de colunas.
Agrupamentos de linha.
Neste tutorial, a tabela de exemplo a seguir será usada.
Captura de tela de uma tabela com colunas que mostram Ano (2020), País (EUA, Panamá ou Canadá), Produto (Camisa ou Shorts), Canal de vendas (Online ou Revendedor) e Unidades (vários valores de 55 a 7500)
Onde encontrar o botão "Agrupar por"
O botão Agrupar por pode ser encontrado em três locais:
Na guia Página Inicial, no grupo Transformar.
Na guia Transformar, no grupo Tabela.
No menu de atalho, quando você clica com o botão direito do mouse para selecionar colunas.
Usar uma função de agregação para agrupar uma ou mais colunas
Neste exemplo, a meta é resumir o total de unidades vendidas nos níveis de país e canal de vendas. Para realizar a operação "Agrupar por", você usará as colunas País e Canal de vendas.
- Selecione Agrupar por na guia Página Inicial.
- Escolha a opção Avançado para selecionar várias colunas para agrupar.
- Selecione a coluna País.
- Clique em Adicionar agrupamento.
- Selecione a coluna Canal de vendas.
- Em Nome da nova coluna, digite Total de unidades; em Operação, selecione Soma; e em Coluna, selecione Unidades.
- Selecione OK
Essa operação gera a tabela a seguir.
Operações disponíveis
Com o recurso Agrupar por, é possível categorizar as operações disponíveis de duas maneiras:
- Operação no nível da linha
- Operação no nível da coluna
Veja na tabela a seguir uma descrição dessas operações.
| Nome de operação | Categoria | Descrição |
|---|---|---|
| Sum | Operação de coluna | Soma todos os valores de uma coluna |
| Média | Operação de coluna | Calcula o valor médio de uma coluna |
| Median | Operação de coluna | Calcula a mediana de uma coluna |
| Min | Operação de coluna | Calcula o valor mínimo de uma coluna |
| Max | Operação de coluna | Calcula o valor máximo de uma coluna |
| Percentil | Operação de coluna | Calcula o percentil usando um valor de entrada de 0 a 100, de uma coluna |
| Contar valores distintos | Operação de coluna | Calcula o número de valores distintos em 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 | Gera todas as linhas agrupadas em um valor de tabela sem agregações |
Observação
As operações Contar valores distintos e Percentil estão disponíveis apenas no Power Query Online.
Realizar uma operação para agrupar por uma ou mais colunas
A partir do exemplo original, neste exemplo, você criará uma coluna que contém 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.
Use estas colunas em Agrupar por:
- País/região
- Canal de vendas
Crie duas novas colunas seguindo estas etapas:
- Agregue a coluna Unidades com a operação Soma. Dê o nome Total de unidades para a coluna.
- Inclua uma nova coluna Produtos com a operação Todas as linhas.
Após a conclusão da operação, veja que a coluna Produtos apresenta valores [Tabela] dentro de cada célula. Cada valor [Tabela] contém todas as linhas agrupadas pelas colunas País e Canal de vendas da tabela original. É possível selecionar o espaço em branco dentro da célula para ver uma pré-visualização do conteúdo da tabela na parte inferior do diálogo.
Observação
O painel de pré-visualização de detalhes pode não mostrar todas as linhas usadas na operação group-by. Selecione o valor [Tabela] para ver todas as linhas pertencentes à operação group-by correspondente.
Em seguida, é necessário extrair a linha com o valor mais alto na coluna Unidades das tabelas dentro da nova coluna Produtos. Depois, dê o nome Produto de melhor desempenho para a nova coluna.
Extrair informações sobre o produto de melhor desempenho
Na nova coluna Produtos com os valores de [Tabela], crie uma coluna personalizada na guia Adicionar coluna na faixa de opções e selecione Coluna personalizada no grupo Geral.
Dê o nome Produto de melhor desempenho à nova coluna. Insira a fórmula Table.Max([Products], "Units" ) em Fórmula de coluna personalizada.
O resultado dessa fórmula cria uma coluna com valores [Registro], que são essencialmente uma tabela com apenas uma linha. Esses registros contêm a linha com o valor máximo da coluna Unidades de cada valor [Tabela] na coluna Produtos.
Com esta nova coluna Produto de melhor desempenho que contém valores [Registro], você pode selecionar o ícone de expansão 
, selecionar os campos Produto e Unidades e então selecionar OK.
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.
Agrupamento difuso
Observação
O recurso está disponível apenas no Power Query Online.
Para demonstrar como fazer um "agrupamento difuso", veja o exemplo de tabela mostrado na imagem a seguir.
O objetivo do agrupamento difuso é realizar uma operação group-by que usa um algoritmo de correspondência aproximado para cadeias de caracteres de texto. O Power Query usa o algoritmo de similaridade Jaccard para medir a semelhança entre pares de instâncias. Em seguida, aplica o clustering hierárquico aglomerativo para agrupar instâncias. A imagem a seguir contém o resultado esperado, em que a tabela é agrupada pela coluna Pessoa.
Para fazer o agrupamento difuso, siga as mesmas etapas descritas anteriormente neste artigo. A única diferença é que, desta vez, na caixa de diálogo Agrupar por, marque a caixa de seleção Usar agrupamento difuso.
Em cada grupo de linhas, o Power Query escolhe a instância mais frequente como "canônica". Se várias instâncias ocorrerem com a mesma frequência, o Power Query escolherá a primeira. Depois de clicar em OK na caixa de diálogo Agrupar por, você receberá o resultado esperado.
No entanto, você tem mais controle sobre a operação de agrupamento difuso com a expansão das Opções de agrupamento difuso.
Confira as opções disponíveis para agrupamento difuso:
- Limite de similaridade (opcional): indica a semelhança que dois valores precisam ter para serem 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: maiúsculas e minúsculas serão ignoradas quando cadeias de caracteres de texto são comparadas. Por padrão, essa opção é ativada.
- Agrupar combinando partes de texto: o algoritmo tenta combinar partes de texto (como combinar Micro e soft em Microsoft) para agrupar valores.
- Mostrar pontuações de similaridade: mostra as pontuações de similaridade entre os valores de entrada e os valores representantes calculados após o agrupamento difuso. Exige a inclusã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 mapeia valores (como mapear MSFT para Microsoft) para agrupá-los.
Nesse exemplo, a tabela de transformação é usada para demonstrar como os valores podem ser mapeados. e tem duas colunas:
- De: a cadeia de caracteres de texto a ser pesquisada na tabela.
- Para: a cadeia de caracteres de texto a ser usada para substituir a cadeia de caracteres de texto na coluna De.
Veja na imagem a seguir a tabela de transformação usada no exemplo.
Importante
É importante que a tabela de transformação tenha as mesmas colunas e nomes de coluna 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.
Volte à caixa de diálogo Agrupar por, expanda Opções de agrupamento difuso, altere a operação de Linhas de contagem para Todas as linhas, marque a opção Mostrar pontuações de similaridade e selecione a Tabela de transformação no menu suspenso.
Depois de selecionar a tabela de transformação, selecione OK. O resultado dessa operação fornece as seguintes informações:
Neste exemplo, a opção Ignorar maiúsculas e minúsculas está marcada; portanto, os valores na coluna De da Tabela de transformação são usados para buscar a cadeia de caracteres de texto sem considerar maiúsculas e minúsculas. Essa operação de transformação ocorre primeiro e, em seguida, a operação de agrupamento difuso é realizada.
A pontuação de similaridade também é mostrada no valor da tabela ao lado da coluna "Pessoa", o que reflete exatamente como os valores foram agrupados e suas respectivas pontuações de similaridade. Você pode expandir essa coluna, se necessário, ou usar os valores das novas colunas Frequência para outros tipos de transformações.
Observação
Ao agrupar por várias colunas, a tabela de transformação realizará 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.