Como usar o Power Query no Microsoft Excel? - Exceldriven

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.

img_01.jpg

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.

img_02.jpg

img_03.jpg

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.

img_04.jpg

Carregamos como uma ligação…

img_05.jpg

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.

img_06.jpg

No processo, escolhemos as tabelas que pretendemos juntar. A Ordem deve ser definida de acordo com a preferência do utilizador.

img_07.jpg

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.

img_08.jpg

img_09.jpg

De seguida redefinimos o nome das colunas e temos uma coluna para a Categoria e outra para o código do produto.

img_10.jpg

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]

img_11.jpg

Agrupamos as colunas por Categoria e por Código…

O resultado final do grupo de registos.

img_13.jpg

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.

img_14.jpg

Alteramos o nome da coluna e obtemos o valor total do trimestre.

img_15.jpg

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.

img_16.jpg

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.

img_18.jpg

img_19.jpg

Atualizamos as tabelas com alguns novos registos…

img_20.jpg

E atualizamos a tabela de resumo…

img_21.jpg

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.

img_22.jpg


Consulte outros conteúdos relevantes:

Microsoft Excel: Conheces as 3 Novas Funções de Texto?

Microsoft Excel: Conheces as 3 Novas Funções de Texto?

Todos os subscritores do Office 365 com as atualizações mais recentes, tem disponíveis 3 novas funções que ajudam imenso a manipular texto.

Continuar a ler...

Função PROCV: Aprenda a obter dados relacionados com um valor pesquisado.

Função PROCV: Aprenda a obter dados relacionados com um valor pesquisado.

Neste novo tutorial, vou mostrar-te tudo o que precisas de saber para usar a função PROCV (VLOOKUP).

Continuar a ler...

 

Pesquisar


Consulte aqui os últimos artigos publicados no nosso blog!

Aceda aqui ao nosso blog!


Consulte aqui os últimos vídeos publicados no nosso canal do Youtube!

Aceda aqui ao nosso arquivo!

Assista, ouça, pratique e aprenda!

Na nossa oferta, disponibilizamos cursos intensivos que lhe dão um conhecimento alargado dos programas, dependendo dos seus objetivos e nível de conhecimento. Para além disso, dispomos também de cursos on-demand que tem, entre outros aspetos, têm como principal objetivo ajudá-lo a resolver problemas específicos do dia-a-dia, sem ter necessidade de assistir a um curso completo.

Aprenda a maximizar o seu tempo e aumente a sua produtividade com a ferramenta mais utilizada em todo o mundo – o Microsoft Excel! Conheça a nossa oferta formação especializada e Ferramentas de Business Intelligence! Vamos lá?!

Microsoft Excel

Fique a conhecer as principais funcionalidades do Microsoft Excel, e ser autónomo no seu trabalho, temos um conjunto de cursos que o podem ajudar a chegar ao seu objetivo!

Veja aqui aos cursos disponíveis!

Business Intelligence

Passe ao próximo nível e conheça a nossa oferta de cursos especializados utilizando as potencialidades de Business Intelligence do Microsoft Excel, ou utilizando o Power Bi Desktop.

Veja aqui os cursos disponíveis!

VBA (Visual Basic for Applications)

Estenda as capacidades do Microsoft Excel, e controle quase a totalidade dos aspetos da aplicação, utilizando o VBA! Uma linguagem de programação à disposição de todos os utilizadores.

Veja aqui os cursos disponíveis!

Subscreva as nossas notícias e novidades!

Tem uma dúvida que gostava de ver esclarecida?

Entre em contacto connosco.

Pretendemos ajudá-lo a trabalhar, de forma eficiente, o Microsoft Excel e as Ferramentas Power Platform (Power BI, Power Apps e Power Automate). O que pretendemos é que possa economizar tempo e aumentar a sua produtividade.

A nossa solução... uma oferta formativa de qualidade e em diversos modelos formativos, com conteúdos práticos, disruptivos e inovadores! Consulte aqui todas as modalidades, ou contacte-nos através do email geral@exceldriven.com. Até breve!

O que os nossos clientes dizem sobre nós?

Depoímentos

Excelente formação, com conteúdos didáticos e exercícios adaptados ao nível dos formandos. Recomendo!
Pramod Maugi -

O formador João Teixeira consegue tornar um assunto à partida monótono, em algo desafiante e cativante. Gostei imenso!
Maria Flores Macedo -

Excelente instrutor, muito bons treinamentos e aquisição de conhecimentos.
Eunice Ramalho -

Formação muito bem organizada e focada para as nossas necessidades. Recomendo.
Pedro Gomes -

Excelente empresa a nível de formação. De realçar o formador Joao Teixeira, profissional 5 estrelas.
Bruno Matos -

Excelente apresentação e organização da Formação em Excel Avançado
Balbina Zambujo -

Os conteúdos são muito bem explicados. As dúvidas dissipadas em curto espaço de tempo.
Rui Filipe -

Tive uma formação de excel fundamental via zoom e, apesar das limitações apresentadas por ser uma formação online, foi ministrada com grande êxito, tendo tido pleno aproveitamento.
Patricia Martins -

Boa tarde, Dou 5 estrelas pois o método de ensino é espetacular, as lições são muito bem sumarizadas, a interação entre o formador e o formando é eficaz possibilitando maior assimilação da matéria, e com o espaço para a resolução de exercícios tornam as aulas mais dinâmicas e proveitosas.
Yara Agostinho -