Tempo de leitura:
Neste artigo vou mostrar-te a ferramenta Agrupar Por [Group By] em Power Query!
Vais ficar a saber como funciona a lógica de Agrupar Dados em Power Query, que permite resumir a informação de uma Tabela em função de um ou mais campos e efetuar cálculos sobre as colunas agrupadas!
Na génese do comando está a função Table.Group e no artigo vais perceber como funciona a sintaxe da função em código M para teres mais controlo sobre os teus dados e vais aprender a utilizar os “parâmetros” e funcionalidades escondidas da ferramenta que não obténs através da interface do utilizador!
Agrupar dados: Básico
Vamos começar por aceder ao Power Query, importando os nossos dados que estão numa tabela.
Já no Power Query, acedemos ao menu Transformar [Transform] -> Opção Agrupar Por [Group By]. É possível também aceder ao comando no Separador Base [Home].
Para agrupar temos dois métodos:
- Básico: Apenas permite agrupar sobre uma coluna e calcular apenas uma coluna.
- Avançado: Permite agrupar por mais do que uma coluna e calcular mais do que uma coluna.
O resultado é o esperado com uma nova tabela, agrupada por [Mês] com a soma da coluna [Total] numa nova coluna chamada “Totais”.
Agrupar dados: Avançado
Vamos voltar a editar o grupo, para realizarmos agora um grupo Avançado.
Para criar um grupo avançado a lógica é a mesma, simplesmente temos mais opções.
Mesmo tendo mais alternativas para criar o grupo, através da interface do utilizador, estamos limitados a escolher apenas 7 funções de agregação mais a possibilidade de mostrar todas as linhas, que mostra todas as linhas do registo em questão, ou seja, uma Tabela aninhada.
O resultado é apresentado na imagem em baixo. Nota os dados inconsistentes na Marca. O Power Query é sensível a Maiúsculas e como tal, considera a marca diferente.
Analisar o Código M
Voltando ao cenário inicial, vamos analisar o código gerado pelo grupo simples:
= Table.Group(
#”Tipo Alterado”,
{“Mês”},
{{“Totais”, each List.Sum([Total]), type nullable number}})
A Função Table.Group é definida por:
- table as table
- key as any
- aggregatedColumns as list
- groupKind as type
- comparer as function
Destes argumentos os primeiros 3 são obrigatórios e os restantes são opcionais. Os 3 primeiros são os que estão disponíveis pela interface do utilizador.
Simplificando a sintaxe a função apenas necessita de:
- Tabela -> normalmente é o resultado do passo anterior
- Key -> A coluna ou colunas usadas para agrupar
- aggregatedColumns -> A(s) coluna(s) a agregar sobre a forma de uma lista com 3 elementos:
- Nome da Coluna a agregar
- Função para agregar
- Tipo de dados do resultado da Coluna
Vamos criar uma referência da Tabela para voltarmos a ter os dados originais e trabalhar a função Table.Group a partir daí. (Na consulta original removemos o passo do grupo para obtermos os dados originais).
No Editor Avançado definimos o código para a nossa função Table.Group.
let
Origem = Dados,
GrupoM = Table.Group (
Origem,
{“Mês”},
{
{“Totais”, each List.Sum([Total]), type number}
}
)
in
GrupoM
Realizar outros cálculos com código M
Como foi possível verificar no exemplo, a interface do utilizador apenas permite realizar um número limitado de cálculos (Agregações).
Contudo através do código M podemos realizar qualquer operação, mesmo que não seja uma agregação.
Através do código podemos também adicionar novas colunas, seguindo o princípio de manter a lista { a. Nome da Coluna a agregar, b. Função para agregar, c. Tipo de dados do resultado da Coluna}
let
Source = Dados,
GrupoM = Table.Group(
Source,
{“Mês”},
{
{“Total de Produtos”, each List.Count([Produto]), type nullable number},
{“Produtos Únicos”, each Text.Combine(List.Distinct([Produto]), “#(cr)”), type text}
}
)
in
GrupoM
Opção Group.Kind
Através do código M é possível também definir o próximo argumento da função que permite definir a forma como o critério do grupo é definido.
- GroupKind.Global: Esta é a opção pré-definida e coloca no mesmo grupo todas as linhas que contêm o mesmo valor. Para esta opção é possível colocar o valor 1 em vez de GroupKind.Global ou deixar o argumento em branco.
- Atenção que o Power Query considera linhas com o mesmo valor considerando as maiúsculas e minúsculas.
- GroupKind.Local: Esta é a opção que permite criar um grupo de linhas consecutivas com o mesmo valor.
let
Source = Dados,
GrupoM = Table.Group(
Source,
{“Marca”},
{
{“Total de Produtos”, each List.Count([Produto]), type nullable number}
},
GroupKind.Global
)
in
GrupoM
Adicionar o comparador
Ainda dentro deste raciocínio, vamos acrescentar já o último argumento: comparer onde podemos definir a forma como a função compara os valores.
Colocando a opção Comparer.OrdinalIgnoreCase em conjunto com a opção anterior GroupKind.Global o grupo é formado com todas as linhas que contêm o mesmo valor e mesmo que as capitulares sejam diferentes, são consideradas o mesmo valor.
let
Source = Dados,
GrupoM = Table.Group(
Source,
{“Marca”},
{
{“Total de Produtos”, each List.Count([Produto]), type nullable number}
},
GroupKind.Global,
Comparer.OrdinalIgnoreCase
)
in
GrupoM
GroupKind.Local
Contudo se pretendemos agrupar por linhas consecutivas de acordo com a ordem da coluna na tabela de dados, devemos colocar a opção GroupKind.Local.
let
Source = Dados,
GrupoM = Table.Group(
Source,
{“Marca”},
{
{“Total de Produtos”, each List.Count([Produto]), type nullable number}
},
GroupKind.Local,
Comparer.OrdinalIgnoreCase
)
in
GrupoM
Agrupar dados criando Tabelas Aninhadas
Vamos agora criar um Grupo, onde vamos usar a Agregação -> Todas as linhas e alterar o código de seguida.
A imagem em baixo mostra o código formatado com o resultado do Grupo.
Na parte da coluna mostro especial atenção para a parte da operação com a Função para agregar que tem a opção each _ que representa a tabela gerada.
Nesta parte podemos realizar uma operação que afeta a tabela interna.
Por exemplo podemos adicionar uma coluna de índice em cada uma das Tabelas internas.
let
Origem = Dados,
GrupoT = Table.Group(
Origem,
{“Mês”},
{
{“Detalhes”,
each Table.AddIndexColumn(_, “Índice”, 1),
type table [
Data=nullable datetime,
Mês=nullable text,
Marca=nullable text,
Produto=nullable text,
Loja=nullable text,
Preco_Un=nullable number,
Quantidade=nullable number,
Total=nullable number]
}
}
)
in
GrupoT
O resultado é o esperado com uma nova coluna em cada uma das tabelas internas (Aninhadas).
Expandir as Tabelas em função do Grupo
O passo natural seguinte é expandir as Tabelas internas para criar uma Tabela Global. Contudo se fizermos este passo, o código gerado é estático e as colunas são definidas de forma explicita, o que não permite que o resultado seja dinâmico.
O resultado é definido com as colunas nomeadas com valores estáticos, que impede de alterar a estrutura da Tabela de base e manter o código funcional.
Para que o resultado seja sempre dinâmico, vamos retirar a opção de Expandir as colunas previamente gerado e vamos adicionar um novo passo na nossa consulta.
CombinarTabelas = Table.Combine(GrupoT[Detalhes])
No novo passo vamos usar a função Table.Combine, onde vamos usar a coluna “Detalhes” que contem as Tabelas internas. Assim podemos combinar todas as tabelas numa única sem mexer nos nomes das colunas.
Qualquer alteração feita nas Tabelas internas ou mesmo na Tabela de Base não vai afetar este passo!
Próximo artigo:
Artigo Anterior:
Artigos por Categoria
- Microsoft Excel (40)
- Power Apps (16)
- Power Automate (3)
- Power BI (10)
- Power Query (12)
- Python (3)
- VBA (7)
Leave a Reply