E aí FERA da analise de dados?! Hoje, vamos desvendar juntos o mistério da soma acumulada no Power Query. Nesse artigo vou te mostrar um guia passo a passo, com uma DAS MUITAS, formas de resolver esse CASE DE NEGÓCIO.
Contexto
Imagine que você está diante de um monte de dados de extratos bancários ou razões contábeis. Cada linha é uma transação, com informações sobre a conta, a data e o valor. E aí, seu chefe aparece e diz: “Quero o saldo acumulado para cada conta, em cada data”. Parece o enredo de um filme de terror, não é? Mas calma, o Power Query está aqui para salvar o dia!
Passo 1: Carregando os Dados
Para facilitar e focarmos em nosso CASE DE NEGÓCIO, abra seu editor Power Query, e invoque uma consulta. A seguir mostro o menu no Excel (por exemplo) do Office 365 em julho/2023:
Agora na barra de fórmulas, digite o comando a seguir:
= Table.TransformColumnTypes(
Table.PromoteHeaders(
Csv.Document("Data,Conta,Histórico,Valor
2023-01-01,Conta1,Depósito,5000
2023-01-01,Conta2,Depósito,6000
2023-01-01,Conta3,Depósito,7000
2023-01-02,Conta3,Compra,-1000
2023-01-02,Conta1,Compra,-200
2023-01-02,Conta2,Compra,-800
2023-01-03,Conta1,Compra,-300
2023-01-03,Conta3,Compra,-600
2023-01-03,Conta2,Compra,-200
2023-01-04,Conta3,Depósito,2500
2023-01-04,Conta1,Depósito,1500
2023-01-04,Conta2,Depósito,2000
2023-01-05,Conta2,Compra,-500
2023-01-05,Conta1,Compra,-750
2023-01-05,Conta3,Compra,-1200")
),
{
{"Data", type date},
{"Valor", type number}
})Isso vai criar nossa base de dados, que eu “baguncei” de propósito, para fixar os ensinamentos que te passarei em cada tópico desse artigo.
Já aqui, você deve ter aprendido uma coisa nova, que é A criação de uma tabela manual.
Tudo tranquilo até agora? Bora lá que tem muito chão neste post…
Passo 2: Ordenando os Dados
Agora, antes de calcular a soma acumulada, precisamos colocar nossos dados em ordem. No Power Query, podemos fazer isso usando a função “Ordenar Crescente” para a coluna da conta, a coluna da data e a coluna do valor.
Como fazer isso? Na guia página inicial temos a seção Classificar.
Ai é só fazer a classificação pela ordem desejada:
- Clique no cabeçalho da coluna CONTA, classifique de A para Z
- Clique no cabeçalho da coluna DATA, classifique de A para Z
- Clique no cabeçalho da coluna VALOR, classifique de A para Z
Ao fazer isso, na barra de fórmulas deve mostrar esse comando aqui, evidenciando a precedência (da esquerda para direita):
= Table.Sort(Fonte,{{"Conta", Order.Ascending}, {"Data", Order.Ascending}, {"Valor", Order.Ascending}})Depois disso, renomeie essa etapa para “TabelaOrdenada”. Isso vai facilitar nossa vida no próximo passo.
Passo 3: Adicionando uma Coluna de Índice
Antes de calcular a soma acumulada, vamos adicionar uma coluna de índice à nossa tabela. Isso nos ajudará a identificar e ordenar nossas linhas de maneira mais eficiente. No Power Query, você pode fazer isso selecionando “Adicionar Coluna” -> “Indice”.
Vá na barra de fórmulas, e altere o nome da coluna para Indice (sem acento)
// de Índice para Indice
= Table.AddIndexColumn(TabelaOrdenada, "Indice", 1, 1)Agora, nossa tabela tem uma nova coluna chamada “Indice”, que começa em 1 e incrementa de 1 em 1 para cada linha.
Renomeie essa etapa para “ColunaIndice”. Isso vai facilitar nossa vida no próximo passo.
Passo 4: Calculando a Soma Acumulada
Agora, chegamos ao momento que todos estavam esperando: a soma acumulada. No Power Query, podemos usar a função List.Accumulate para isso.
Crie uma nova etapa no editor, e nomeie ela para “SomaAcumulada”:
Deverá ficar como na imagem a seguir:
Agora, vá na barra de fórmulas, e cola esse comando abaixo:
= Table.AddColumn(
ColunaIndice,
"SomaAcumulada", each
// isolando as variáveis de cada ambiente
let
conta = [Conta],
indice = [Indice]
in
List.Accumulate(
Table.SelectRows(
ColunaIndice, each
[Conta] = conta
and [Indice] <= indice
)[Valor],
0,
(estado, atual) => estado + atual
)
)Neste código, estamos dizendo ao Power Query para somar os valores de todas as linhas que têm a mesma conta e uma data e valor menores ou iguais à linha atual. Parece complexo, mas é simples.
Vou tentar explicar, para que você possa copiar e replicar em seu ambiente de trabalho:
- Table.AddColumn(ColunaIndice, “SomaAcumulada”, each … ): Esta é a função principal que estamos usando. Ela adiciona uma nova coluna à tabela “ColunaIndice”. O nome da nova coluna é “SomaAcumulada”. O valor de cada linha nesta coluna é calculado pela função que vem depois do each.
- let conta = [Conta], indice = [Indice] in …: Aqui estamos criando duas variáveis, conta e indice, que armazenam os valores da coluna “Conta” e “Indice” da linha atual, respectivamente. Estas variáveis são usadas na função List.Accumulate que vem a seguir.
- List.Accumulate(…, 0, (estado, atual) => estado + atual): Esta é a função que calcula a soma acumulada. Ela começa com um valor inicial de 0 e, para cada item na lista (que é gerada pela função Table.SelectRows), ela adiciona o valor atual (atual) ao total acumulado até agora (estado).
- Table.SelectRows(ColunaIndice, each [Conta] = conta and [Indice] <= indice)[Valor]: Esta função seleciona as linhas da tabela “ColunaIndice” que têm o mesmo valor na coluna “Conta” que a linha atual e um valor na coluna “Indice” que é menor ou igual ao da linha atual. Ela retorna uma lista dos valores na coluna “Valor” para essas linhas. Esta lista é a entrada para a função List.Accumulate.
Passo 5: Delete a Coluna Indice
Agora que já calculamos a soma acumulada, a coluna de índice que criamos não é mais necessária. Vamos removê-la para deixar nossa tabela mais limpa. No Power Query, você pode fazer isso selecionando a coluna “Índice” e depois clicando em “Remover Colunas”.
E pronto! Agora temos uma tabela limpa e organizada, com a soma acumulada para cada conta e data.
Flashback: Um de meus primeiros vídeos
Antes de concluir, CLIQUE AQUI para dar uma olhada em um dos meus primeiros vídeos chamado de “COMO FAZER SOMA ACUMULADA NO POWER QUERY“.
Ah, os velhos tempos… Eu era tão tímido! No vídeo, eu discuto a soma acumulada no Power Query com um exemplo prático.
Dou ênfase à importância de organizar os dados por conta e data e discuto a utilização de uma tabela CSV do Excel para dados que não estão em um banco de dados ou SQL.
O vídeo é uma excelente fonte de informações para quem está começando a usar o Power Query e deseja aprender mais sobre a soma acumulada.
Essa é para os FERAS: Melhorando a Performance com Buffer
Agora que já dominamos a soma acumulada, que tal darmos um passo adiante e melhorarmos a performance do nosso código? Para isso, vamos usar uma técnica chamada “buffering”.
O buffering é uma técnica que permite ao Power Query armazenar temporariamente os dados em memória, o que pode melhorar significativamente a performance ao lidar com grandes volumes de dados. Isso acontece porque, ao usar o buffer, o Power Query não precisa recarregar os dados a cada operação, o que pode ser bastante demorado em conjuntos de dados grandes.
No nosso caso, podemos usar o buffering para armazenar a tabela ordenada em memória antes de calcular a soma acumulada. Isso pode fazer uma grande diferença na performance, especialmente se estivermos lidando com muitas linhas de dados.
Para implementar o buffering, podemos usar a função Table.Buffer, e pra isso basta colocá-la antes da soma acumulada, assim:
// ...
// Era Assim
// ColunaIndice = Table.AddIndexColumn(TabelaOrdenada, "Indice", 1, 1, Int64.Type),
// Ficou assim
ColunaIndice = Table.Buffer(Table.AddIndexColumn(TabelaOrdenada, "Indice", 1, 1, Int64.Type)),
// ... Talvez você não sinta diferença em conjuntos de dados pequenos, ou se tem poucas etapas anteriores, mas, quanto mais robusto for o tratamento (e volume) de dados anterior à soma acumulada, a aplicação do Buffer é muito importante.
Na LIVE 005 nós trouxemos um caso de cálculo de ruptura de estoques, o qual careceu de soma acumulada, onde aplicamos outra técnica de acumulo (sim, existe mais de uma forma de fazer), e também mostramos NA PRÁTICA o efeito do Buffer em uma tabelinha simples de 1000 linhas (só mil linhas).
Hoje essa LIVE e outras mais, não estão mais abertas no Youtube, mas estão disponíveis no módulo LIVES na FORMAÇÃO POWER QUERY 2.0, que é um pacote de mais de 15 cursos (em 2023), focados no analista de dados que está vindo do Excel.
TRILHA COMPLEMENTAR
Conclusão
E aí está, pessoal! Agora você tem uma nova coluna em seu conjunto de dados com o saldo acumulado para cada conta, em cada data. Com o Power Query, até mesmo a temida soma acumulada se torna uma tarefa simples.
Mas não pare por aqui! Domine o Power Query com a FORMAÇÃO POWER QUERY 2.0. Essa formação vai te transformar em um verdadeiro mestre do Power Query. Não perca essa oportunidade de levar suas habilidades de análise de dados para o próximo nível.
Espero que este post tenha sido útil (e divertido) para você. Continue nos acompanhando para mais dicas, truques e sarcasmo sobre análise de dados.
Forte abraço do
