Soma Acumulada no Power Query: Guia Prático!

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.

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.

Quer ver essa técnica na prática?

Se você chegou até aqui, ótimo. Mas quer mesmo entender como aplicar tudo isso visualmente, com exemplos reais?

👉 No nosso canal do YouTube tem um vídeo completo mostrando a técnica de soma acumulada no Power Query passo a passo.

É direto ao ponto, sem enrolação, com explicações visuais e situações reais do dia a dia.

Automatize ainda mais com a DataEvo

Agora que você já domina uma técnica poderosa, imagine aprender dezenas de outras que vão transformar sua rotina com planilhas. O DataEvo é uma formação completa, prática e direta, pensada especialmente para quem quer automatizar tarefas no Excel, Power BI e muito mais.

• Domine Power Query, SQL, DAX e até Python
• Reduza drasticamente o retrabalho
• Economize horas por semana com soluções automatizadas

Clique aqui e conheça o DataEvo

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 o DataEvo. Essa formação vai te transformar em um verdadeiro mestre do tratamento e automação de Dados. 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