Transformar Saldo Acumulado em Movimento Mensal com Power Query

Vamos falar sobre como transformar saldos acumulados em movimentos mensais de maneira automática, sem precisar ficar subtraindo coluna por coluna manualmente. Imagine que você tem uma tabela com saldos mensais acumulados e precisa convertê-los em movimentos mensais para facilitar a análise.

Neste tutorial, vamos mostrar como fazer isso usando Power Query. O objetivo é automatizar o processo, garantindo que, mesmo que novas colunas sejam adicionadas, o cálculo seja atualizado automaticamente.

O Problema dos Saldos Acumulados

O problema é simples: você tem uma tabela com contas e saldos acumulados por mês. Por exemplo, em janeiro o saldo é 100, em fevereiro é 210, em março é 290, e assim por diante. O que você realmente precisa é saber o movimento mensal, ou seja, quanto foi movimentado de um mês para o outro.

Vamos exemplificar com uma tabela de um aluno, Artur, que trouxe essa questão: ele tem uma tabela onde os saldos acumulados são apresentados por mês. A necessidade é transformar esses saldos acumulados em movimentos mensais. Vamos resolver esse problema usando Power Query.

Visualmente fica mais fácil entender

O exemplo de nosso aluno se trata de uma DRE, mas, nós resumimos na tabela a seguir, para focar unicamente no problema:

Transformamos ela nessa outra tabela, sem fazer cálculos manuais, ou seja, a base de dados pode crescer a vontade, que não ficamos presos às colunas do case inicial.

Preparação dos Dados

Primeiro, vamos entender como os dados estão estruturados e como devemos prepará-los para a transformação.
A tabela tem as seguintes colunas:

• Conta
• Descrição da Conta
• Saldo acumulado de Janeiro
• Saldo acumulado de Fevereiro
• Saldo acumulado de Março
• …

Nosso objetivo é pegar essas colunas de saldo acumulado e transformá-las em movimentos mensais. Vamos ver como fazer isso passo a passo.

Passo a Passo da Solução

No final desse artigo temos o material de apoio com a planilha usada nessa aula, para que você possa baixar e acompanhar.

Basta solicitar, que em até 5 minutos estará no seu email.

1. Importando os Dados para o Power Query

O primeiro passo é importar a tabela para o Power Query.
Para isso, siga esses passos:

1. Abra o Excel e vá para a guia “Dados”.
2. Selecione “Obter Dados” e escolha a fonte de dados onde sua tabela está armazenada (arquivo Excel, pasta de trabalho, etc.).
3. Selecione a tabela desejada e clique em “Carregar para” e escolha “Power Query”.

Ou abra o editor Power Query e importe a planilha com os dados.

2. Transformando Colunas de Saldos em Linhas

Para facilitar a manipulação dos dados, vamos transformar as colunas de saldos acumulados em linhas:

1. Selecione todas as colunas de saldo (de Janeiro a Dezembro, por exemplo).
2. Vá para a guia “Transformar” e escolha “Despivotar Colunas”.
3. Isso transformará todas as colunas de saldo em duas colunas: “Atributo” (o nome do mês) e “Valor” (o saldo acumulado).

3. Substituindo Valores Nulos por Zero

Para evitar problemas durante a subtração, substituímos todos os valores nulos por zero.
Uma etapa antes do passo anterior (Colunas para Linhas), faça isso:

1. Na guia “Transformar”, escolha “Substituir Valores”.
2. Substitua todos os valores nulos por zero.

Olhe a tabela despivotada, e verás que agora todos os meses possuem valor.

5. Agrupando Dados por Conta

Agora, agrupamos os dados por conta para calcular os movimentos mensais dentro de cada conta:

1. Selecione a coluna “Conta”.
2. Vá para “Transformar” e escolha “Agrupar por”.
3. Agrupe por “Conta” e crie uma nova coluna que contenha todas as linhas agrupadas. De o nome [dados] para ela

Isso vai criar uma coluna com subtabelas, sendo uma para as linhas correspondentes àquela conta.

4. Adicionando Coluna de Índice nas Subtabelas

Adicionamos uma coluna de índice para manter a ordem dos meses e facilitar a subtração:

6. Calculando os Movimentos Mensais

Aqui vem a parte mais interessante: calcular os movimentos mensais subtraindo o saldo acumulado do mês anterior do saldo acumulado do mês atual:

1. Adicione uma nova coluna personalizada dentro do agrupamento.
2. Dê o nome [dados3] para ela, e inclua essa função

Essa função vai calcular a diferença da linha atual com a linha anterior, como representado nessa imagem:

7. Agora é só Expandir os Dados e Deletar o que não quer

Depois de calcular os movimentos mensais, expandimos as tabelas agrupadas e reorganizamos os dados:

1. Expanda a coluna que contém as tabelas agrupadas.
2. Selecione as colunas “Conta”, “Descrição da Conta”, “Atributo” (mês), e “Movimento Mensal”.
3. Reorganize as colunas conforme necessário.

O pulo do gato 🐈

Preparamos uma aula onde te mostro o isolamento de cálculo em memória para usar no pivotamento, que é uma das formas que ensinamos na FORMAÇÃO POWER QUERY 2.0, que aumentam muito a performance das consultas.

Assista todas as explicações visuais e a prática que te mostramos em cada passo mencionado aqui.

Formação Power Query 2.0: Domine a Automação

Ganhe tempo e aprenda a automatizar tarefas no Excel, Power Query, SQL, DAX e Python! Não perca a oportunidade de se inscrever na nossa Formação Power Query 2.0.

Com essa formação, você aprenderá a transformar dados de maneira eficiente, melhorando sua produtividade e permitindo análises mais avançadas.

Essa formação é ideal para analistas de negócios que querem elevar suas habilidades e automatizar processos repetitivos.

Material de Apoio

Logo aqui no final você pode solicitar o envio do material para seu e-mail.
Solicite, que em até 5 minutos você receberá.

Conclusão

Neste tutorial, vimos como transformar saldos acumulados em movimentos mensais utilizando Power Query.
Através de uma série de passos detalhados, conseguimos automatizar o processo de cálculo, garantindo precisão e eficiência.

Esta técnica é especialmente útil para analistas de negócios que lidam com grandes volumes de dados e necessitam de análises precisas e rápidas.

Abraço,