Tempo de leitura:
Neste artigo vou mostrar como podes somar várias colunas de uma tabela em Power Query de forma dinâmica, permitindo que a tua tabela possa ter mais ou menos colunas, mudar os nomes, e mesmo assim continuar a calcular sem erros!
Adicionar colunas calculadas em Power Query é um processo bastante simples, mas quando utilizado de forma incorreta, gera erros porque as colunas usadas para calcular são explicitamente definidas no cálculo. Quando a estrutura da tua tabela muda, vais ser confrontado com erros no passo onde o cálculo é definido.
Vamos então ver como podemos aplicar totais de forma dinâmica numa tabela.
Importar os dados
Vamos começar por importar os dados através do separador Dados
Acedemos ao ficheiro
Nas definições da consulta, alteramos o nome da Query e removemos o passo Tipo Alterado, que identifica cada coluna e coloca o tipo de dados. Este passo poderá ser feito no final da consulta.
Definir a Soma
Podemos aplicar a soma de uma forma simples, acedendo ao Menu Adicionar Colunas, com o conjunto de colunas selecionado, e escolher a opção Padrão -> Adicionar.
Ao realizarmos esta operação obtemos a nova coluna de forma simples.
Mas a coluna é definida com um cálculo “estático”, onde as colunas a somar são explicitas na fórmula. Pretendemos mudar este comportamento!
Vamos então perceber como podemos realizar o cálculo de forma dinâmica.
Passo 1 – Obter o nome das colunas da tabela
Voltamos ao passo anterior removendo esta soma, e vamos apenas alterar o nome do passo “Cabeçalhos Promovidos” para “Tabela” para simplificar a lógica.
Inserimos um novo passo para obter o nome das colunas de forma dinâmica.
Para esta opção vamos utilizar a função Table.ColumnNames onde é retornada uma lista com o nome das colunas da tabela.
= Table.ColumnNames ( Tabela )
Nesta lista precisamos de realizar mais algumas alterações, nomeadamente retirar os itens (nomes dos campos) que não vão ser usados para somar.
Para esta lógica podemos usar a função List.Difference, que retira de uma lista, valores de outra lista. A lista é composta pelos nomes das colunas a retirar.
= List.Difference ( NomesColunas, {“Mercado”, “Descricao_Produto”} )
Agora que já temos a lista com as colunas a somar, necessitamos de voltar à tabela para adicionarmos a nova coluna.
Passo 2 – Criar a nova coluna calculada
Para adicionar a nova coluna vamos utilizar a função Table.AddColumn. Mas precisamos de trabalhar sobre a lista de campos, para conseguirmos obter os valores a somar. Precisamos de incluir mais 2 funções:
- List.Transform: Para aplicarmos uma transformação a cada item da lista, neste caso cada campo com o mês.
- Record.Field: Esta função permite obter o valor de um campo num registo.
Agora a parte complexa é a seguinte. A função Record.Field necessita de um registo. O registo faz parte de uma tabela, e não de uma lista, sendo que esta função deve ser calculada no contexto da tabela e não no contexto da lista. Por isso necessitamos de criar uma função personalizada para avaliar o contexto da coluna da tabela e não do item da lista.
Vamos então ver tudo aplicado na fórmula.
Para este cenário vou mostrar no Editor Avançado para ser mais fácil interpretar o código.
Já no editor avançado aplicamos a nossa fórmula. Na imagem podemos verificar o código comentado para ser mais fácil de interpretar.
SomaDinamica =
Table.AddColumn (
Tabela
“Total Dinamico”,
each List.Sum (
List.Transform (
ColunasSoma,
(coluna) => Record.Field ( _, coluna )
)
),
type number
)
Esta lógica permite aplicar o cálculo independentemente do número de colunas a calcular.
Para além desta questão pode ser aplicada qualquer função de agregação para além da função List.Sum.
Se atualizarmos os dados na origem, o cálculo funciona na perfeição.
Próximo artigo:
Artigo Anterior:
Artigos por Categoria
Artigos por Categoria
- Microsoft Excel (43)
- Power Apps (16)
- Power Automate (3)
- Power BI (11)
- Power Query (14)
- Python (3)
- VBA (7)
Leave a Reply