Aprenda aqui a usar o Power Query no Microsoft Excel. Uma das ferramentas mais importantes para automatizar processos de transformação de dados utilizando o Microsoft Excel ou o Power Bi Desktop.
Neste artigo, mostro-te como podes utilizar o Power Query para automatizar o processo de compilação de dados provenientes de várias tabelas, existentes no mesmo ficheiro de Excel, juntando os vários registos de cada tabela, sem necessidade de recorrer ao Copiar-Colar.
Juntamente com a junção de várias linhas, vamos ver ainda algumas transformações, bastante simples e intuitivas, que são possíveis de fazer com apenas alguns cliques em determinados comandos do Power Query. Por exemplo, que permitem agrupar e agregar os registos em função de uma ou mais colunas e ainda adicionar novas colunas calculadas.
O resultado final é uma tabela, que resume toda a informação e é atualizada de uma forma regular sempre que surgem novos registos em qualquer uma das tabelas. Não percas aqui todo o processo passo-a-passo. Alguma dúvida envia-nos a tua mensagem... ficamos à tua espera!
Cenário:
O Ficheiro base é composto por 3 folhas cada uma com uma tabela.
Passo 1: Importar cada uma das tabelas para o Power Query
Começamos o processo por obter dados de uma Tabela / Intervalo, que permite converter cada um dos intervalos de cada folha, numa tabela, que é importada para o Power Query e serve como base para a Query final.
No Power Query, apenas carregamos a Query novamente para o Excel, mas sobre a forma de uma ligação aos dados, uma vez que a Tabela já existe no ficheiro.
Carregamos como uma ligação…
Repetimos o processo para todas as tabelas do ficheiro que pretendemos compilar.
Passo 2: Criar uma consulta, baseada nas 3 consultas [queries] de originais.
No menu Dados [Data] acedemos ao Botão Obter Dados [Get Data] e à opção Combinar Consultas [Combine Queries] escolhendo a opção: Acrescentar [Append].
Esta opção permite combinar os registos de todas as tabelas numa única tabela.
No processo, escolhemos as tabelas que pretendemos juntar. A Ordem deve ser definida de acordo com a preferência do utilizador.
Depois de conformar as opções, é gerada uma nova consulta [Query] com a informação de todas as bases.
Passo 3: Separar a categoria do código do produto.
Depois de termos a tabela com todos os registos juntos, vamos começar o processo de transformação dos dados. Uma das primeiras transformações consiste na divisão da coluna do ID de produto em duas colunas, uma que representa a categoria do produto e outra o código do produto.
De seguida redefinimos o nome das colunas e temos uma coluna para a Categoria e outra para o código do produto.
Passo 4: Transformar os dados: Agrupar e agregar os itens por produto.
A segunda transformação que vamos implementar é agrupar por Categoria e por Produto os valores de cada mês (Jan, Fev e Mar) agregando pela SOMA os seus valores.
No separador Transformar [Transform] acedemos ao comando Agrupar Por [Group by]
Agrupamos as colunas por Categoria e por Código…
O resultado final do grupo de registos.
Passo 5: Gerar uma nova coluna que soma os valores de cada mês.
O próximo passo de transformação de dados vai permitir gerar uma nova coluna que resulta da soma das 3 colunas anteriores. Existem múltiplas operações simples que se podem efetuar diretamente no Power Query, entre elas, somas e multiplicações ou mesmo obter percentagens de determinados valores. As colunas devem ser selecionadas previamente, e a ordem da seleção, interfere no cálculo. No caso da soma, é indiferente, mas numa subtração ou divisão de valores, a ordem da seleção das colunas já é importante.
Selecionado as colunas de base, acedemos ao menu Adicionar Colunas [Add columns] e às opções de Padrão para adicionar uma SOMA.
Alteramos o nome da coluna e obtemos o valor total do trimestre.
Passo 6: Ordenar os dados por categoria e por Código.
Para apresentamos o relatório final, vamos organizar as colunas da tabela, ordenando por categoria e código.
Mesmo sem outras categorias na tabela final, o passo fica guardado. Assim, quando atualizados os dados com novos registos, o passo é executado e ordena os dados na fase em que a tabela é refrescada.
Passo 7: Fechar, carregar os dados para o Excel e atualizar as tabelas.
Depois de todas as transformações, é a parte em que carregamos os dados para o Excel, desta vez para uma tabela, numa nova folha e testamos a atualização dos mesmos.
Atualizamos as tabelas com alguns novos registos…
E atualizamos a tabela de resumo…
A tabela de resumo tem todos os novos registos, agrupados por categoria e código, com a nova coluna que soma os valores do trimestre e ordenados por ordem alfabética.
Consulte outros conteúdos relevantes:
Tipos de junção no Power Query (Join Kinds)
Neste novo vídeo vais aprender como podes utilizar os vários tipos de junção (Join Kinds), para analisar se o valor de uma determinada célula existe num intervalo.
VBA e função FILTRAR: Exportar um relatório para PDF
Neste novo vídeo, vou mostrar-te como podes usar a função FILTRAR para obter um conjunto de dados sobre um determinado critério.