Com o Power Query, trabalhar com dados dispersos em planilhas ou mesmo pastas de trabalho se tornou mais fácil.
Uma das coisas em que o Power Query pode economizar muito tempo é quando você precisa mesclar tabelas com tamanhos diferentes e colunas com base em uma coluna correspondente.
Abaixo está um vídeo onde mostro exatamente como mesclar tabelas no Excel usando o Power Query.
Caso prefira ler o texto a assistir a um vídeo, abaixo estão as instruções escritas.
Suponha que você tenha uma tabela conforme mostrado abaixo:
Esta tabela contém os dados que desejo usar, mas ainda faltam duas colunas importantes - o ‘ID do produto’ e a ‘Região’ onde o representante de vendas opera.
Essas informações são fornecidas em tabelas separadas, conforme mostrado abaixo:
Para obter todas essas informações em uma única tabela, você terá que mesclar essas três tabelas para que possa criar uma Tabela Dinâmica e analisá-la ou usá-la para outros fins de relatório / painel.
E por mesclar, não quero dizer um simples copiar e colar.
Você terá que mapear os registros relevantes da Tabela 1 com os dados das Tabelas 2 e 3.
Agora você pode contar com VLOOKUP ou INDEX / MATCH para fazer isso.
Ou, se você for um gênio do VBA, pode escrever um código para fazer isso.
Mas essas opções são demoradas e complicadas em comparação com o Power Query.
Neste tutorial, mostrarei como mesclar essas três tabelas do Excel em uma.
Para que essa técnica funcione, você precisa ter colunas de conexão. Por exemplo, na Tabela 1 e na Tabela 2, a coluna comum é ‘Item’, e na Tabela 1 e Tabela 3, a coluna comum é ‘Representante de Vendas’. Além disso, observe que não deve haver repetição nessas colunas de conexão.Observação: o Power Query pode ser usado como um suplemento no Excel 2010 e 2013 e é um recurso embutido do Excel 2016 em diante. Com base na sua versão, algumas imagens podem parecer diferentes (as capturas de imagens usadas neste tutorial são do Excel 2016).
Mesclar tabelas usando o Power Query
Nomeei essas tabelas conforme mostrado abaixo:
- Tabela 1 - Sales_Data
- Mesa 2 - Pdt_Id
- Tabela 3 - Região
Não é obrigatório renomear essas tabelas, mas é melhor dar nomes que descrevam do que trata a tabela.
De uma vez, você pode mesclar apenas duas tabelas no Power Query.
Portanto, primeiro teremos que mesclar a Tabela 1 e a Tabela 2 e, em seguida, mesclar a Tabela 3 nelas na próxima etapa.
Mesclando a Tabela 1 e a Tabela 2
Para mesclar tabelas, primeiro você precisa converter essas tabelas em conexões no Power Query. Assim que tiver as conexões, você pode mesclá-las facilmente.
Aqui estão as etapas para salvar uma tabela do Excel como uma conexão no Power Query:
- Selecione qualquer célula na tabela Sales_Data.
- Clique na guia Dados.
- No grupo Get & Transform, clique em ‘From Table / Range’. Isso abrirá o editor de consultas.
- No editor de consultas, clique na guia ‘Arquivo’.
- Clique na opção ‘Fechar e carregar para’.
- Na caixa de diálogo ‘Importar dados’, selecione ‘Somente criar conexão’.
- Clique OK.
As etapas acima criariam uma conexão com o nome Sales_Data (ou qualquer nome que você tenha dado à Tabela do Excel).
Repita as etapas acima para a Tabela 2 e Tabela 3.
Então, quando terminar, você terá três conexões (com o nome Sales_Data, Pdt_Id e Region).
Agora vamos ver como mesclar as tabelas Sales_Data e Pdt_Id.
- Clique na guia Dados.
- No grupo Get & Transform Data, clique em Get Data.
- No menu suspenso, clique em Combinar consultas.
- Clique em Merge. Isso abrirá a caixa de diálogo Mesclar.
- Na caixa de diálogo Mesclar, selecione ‘Sales_Data’ no primeiro menu suspenso.
- Selecione ‘Pdt_Id’ no segundo menu suspenso.
- Na visualização de ‘Sales_Data’, clique na coluna ‘Item’. Isso selecionará a coluna inteira.
- Na visualização ‘Pdt_Id’, clique na coluna ‘Item’. Isso selecionará a coluna inteira.
- Na lista suspensa ‘Join Kind’, selecione ‘Left Outer (all from first, matching from second)’.
- Clique OK.
As etapas acima abririam o editor de consultas e mostrariam os dados de Sales_Data com uma coluna adicional (de Pdt_Id).
Mesclando as tabelas do Excel (Tabela 1 e 2)
Agora, o processo de mesclagem das tabelas acontecerá no editor de consultas com as seguintes etapas:
- Na coluna adicional (Pdt_Id), clique na seta de duas pontas no cabeçalho.
- Na caixa de opções que é aberta, desmarque todos os nomes de coluna e selecione apenas Item. Isso ocorre porque já temos a coluna de nome do produto na tabela existente e queremos apenas o ID do produto para cada produto.
- Desmarque a opção ‘Usar nome da coluna original como prefixo’.
- Clique OK.
Isso forneceria a tabela resultante que contém todos os registros da tabela Sales_Data e uma coluna adicional que também possui ids de produto (da tabela Pdt_Id).
Agora, se você quiser apenas combinar duas tabelas, pode carregar este Excel, pronto.
Mas temos três tabelas para mesclar, então há mais trabalho a ser feito.
Você precisa salvar esta tabela resultante como uma conexão (para que possamos usá-la para mesclá-la com a Tabela 3).
Aqui estão as etapas para salvar esta tabela mesclada (com dados da tabela Sales_Data e Pdt_Id) como uma conexão:
- Clique na guia Arquivo
- Clique na opção ‘Fechar e carregar para’.
- Na caixa de diálogo ‘Importar dados’, selecione ‘Somente criar conexão’.
- Clique OK.
Isso salvará os dados recém-mesclados como uma conexão. Você pode renomear esta conexão se desejar.
Mesclando a Tabela 3 com a Tabela Resultante
O processo de mesclar a terceira tabela com a tabela resultante (que obtivemos mesclando a Tabela 1 e a Tabela 2) é exatamente o mesmo.
Aqui estão as etapas para mesclar essas tabelas:
- Clique na guia Dados.
- No grupo Get & Transform Data, clique em ‘Get Data’.
- No menu suspenso, clique em ‘Combinar consultas
- Clique em ‘Merge’. Isso abrirá a caixa de diálogo Mesclar.
- Na caixa de diálogo Merge, selecione ‘Merge1’ no primeiro menu suspenso.
- Selecione ‘Região’ no segundo menu suspenso.
- Na visualização de ‘Merge1’, clique na coluna ‘Sales Rep’. Isso selecionará a coluna inteira.
- Na visualização da região, clique na coluna ‘Representante de vendas’. Isso selecionará a coluna inteira.
- No menu suspenso ‘Join Kind’, selecione Left Outer (todos do primeiro, combinando do segundo).
- Clique OK.
As etapas acima abririam o editor de consultas e mostrariam os dados de Merge1 com uma coluna adicional (região).
Agora, o processo de mesclagem das tabelas acontecerá no editor de consultas com as seguintes etapas:
- Na coluna adicional (Região), clique na seta de duas pontas no cabeçalho.
- Na caixa de opções que é aberta, desmarque todos os nomes de coluna e selecione apenas Região.
- Desmarque a opção ‘Usar nome da coluna original como prefixo’.
- Clique OK.
As etapas acima dariam a você uma tabela que tem todas as três tabelas mescladas (tabela Sales_Data com uma coluna para Pdt_Id e outra para Região).
Aqui estão as etapas para carregar esta tabela no Excel:
- Clique na guia Arquivo.
- Clique em ‘Fechar e carregar para’.
- Na caixa de diálogo ‘Importar dados’, selecione as opções Tabela e Novas planilhas.
- Clique OK.
Isso lhe daria a tabela resultante mesclada em uma nova planilha.
Uma das melhores coisas sobre o Power Query é que você pode facilmente acomodar quaisquer alterações nos dados subjacentes (Tabela 1, 2 e 3) simplesmente atualizando-os.
Por exemplo, suponha que Laura seja transferida para a Ásia e você obtenha novos dados para o próximo mês. Agora você não precisa repetir as etapas acima novamente. Tudo o que você precisa fazer é atualizar a mesa e ela fará tudo de novo para você.
Em segundos, você terá a nova tabela mesclada.
Você também pode gostar dos seguintes tutoriais do Power Query:
- Combine dados de várias pastas de trabalho no Excel (usando o Power Query).
- Combine dados de várias planilhas em uma única planilha no Excel.
- Como remover dados dinâmicos no Excel usando o Power Query (também conhecido como Get & Transform)
- Obtenha uma lista de nomes de arquivo de pastas e subpastas (usando o Power Query)
