Mesclar Consultas no Power Query Excel – PROCV Entre Planilhas

Hoje eu quero te mostrar como mesclar consultas no Power Query Excel para construir um PROCV sem fórmulas usando essa ferramenta!

Mesclar consultas no Power Query é o recurso que combina duas tabelas a partir de uma coluna em comum — funcionando como um PROCV, mas sem fórmulas. Você seleciona as colunas-chave, escolhe o tipo de junção e o Power Query traz as informações de uma planilha para a outra. Como o tratamento fica salvo, basta atualizar para reaplicar tudo aos novos dados.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

O que você aprende neste vídeo

Resposta rápida: A aula constrói um projeto completo: mescla duas planilhas (cadastro de vendas e cadastro de clientes) pelo código do cliente comum, calcula a idade a partir da data de nascimento, classifica cada cliente numa faixa etária, e mostra como tudo isso se atualiza sozinho quando você atualiza a consulta depois de lançar vendas novas.

Neste vídeo (20 min):

• 0:41 - No exemplo, a planilha de vendas registra as vendas de 1º de janeiro de 2017 a 31 de dezembro de 2021, mais de 6 mil linhas.
• 3:05 - Mostra a tabela final que quer construir (a ‘tabela gabarito’): data, produto, pagamento e parcelas, com nome, nascimento, idade, categoria e UF do cliente puxados pelo código, como um PROCV.
• 8:02 - Recomeçando do zero, importa pelo caminho Dados, Obter Dados, De Arquivo, Da Pasta de Trabalho o arquivo de origem, usando ‘Selecionar vários itens’ para trazer as duas planilhas de uma vez.
• 9:58 - Na guia Página Inicial, grupo Combinar, abre Mesclar Consultas com as duas tabelas lado a lado.
• 10:27 - Escolhe ‘código do cliente’ como a coluna em comum nas duas tabelas: a chave que funciona como um PROCV.
• 10:52 - Expande a coluna que a mesclagem criou, escolhe nome, data de nascimento e Unidade Federativa, e desmarca ‘usar o nome da coluna original como prefixo’ para manter os nomes originais.
• 12:03 - Transforma a coluna de data de nascimento em idade pela guia Transformar, grupo Coluna de Data e Hora, opção Idade, e depois Duração, Total de Anos.
• 14:04 - Cria a coluna ‘Categoria do Cliente’ com uma Coluna Condicional: abaixo de 30 anos, entre 30 e 60, ou acima de 60.
• 15:56 - Usa ‘Fechar e Carregar’ para trazer a tabela já mesclada e tratada de volta para o Excel.
• 18:17 - Depois de lançar uma venda nova nas planilhas de origem e clicar em Atualizar Tudo, a linha nova aparece na tabela final já com idade, categoria e UF preenchidas.

Trechos do vídeo:

• A idade calculada a partir da data de nascimento sai com casas decimais (por exemplo, 29,9 anos), e o certo é arredondar para baixo, não para o mais próximo: com 29,9 anos a pessoa ainda não fez aniversário, então ainda tem 29.
• Logo depois de mesclar, a coluna nova aparece cheia de Table repetido: ela só vira dado quando se expande a coluna e se escolhem os campos que vão entrar.
• A Coluna Condicional do Power Query funciona como a função SE do Excel, e lê as regras sempre de cima para baixo: quem já passou pela primeira condição não precisa repetir o limite de novo na segunda.
• Reabrindo o arquivo daqui a dois anos, por exemplo, e clicando em Atualizar Tudo, a idade e a categoria de cada cliente recalculam pela data atual, mesmo sem nenhuma venda nova.

Para receber por e-mail o(s) arquivo(s) que utilizamos na aula, preencha:

Mesclar Consultas no Power Query

Hoje eu quero te mostrar o suplemento do Excel chamado Power Query e te mostrar como você pode mesclar consultas no Excel.

Vamos fazer uma espécie de PROCV só que utilizar o mesclar consultas no Power Query, então você vai conseguir fazer o PROCV de uma forma mais fácil e sem utilizar fórmulas.

PROCV Entre Planilhas no Power Query

Antes de começar nossas análises é importante darmos uma olhada nas bases de dados para que você saiba o que temos nelas e quais informações nós vamos trazer de uma tabela para a outra.

Na primeira tabela nós temos um cadastro de vendas, onde temos todas as informações das vendas, como: data, nome do produto, forma de pagamento, código do cliente e a quantidade de parcelas.

Nessa segunda base de dados nós temos as informações dos clientes, então vamos ter o código do cliente, que é o mesmo da base anterior, nome do cliente, data de nascimento e UF do cliente.

Agora imagine que você queira juntar essas informações, pra ter todas elas em uma única tabela, como você faria?

Uma das opções seria utilizar o PROCV, mas você teria que fazer isso para as 3 colunas e criar fórmulas nas 3 colunas.

Nós vamos utilizar o Power Query para fazer isso e ainda vamos fazer o cálculo de idade e categorizar os nossos clientes com base na idade. Isso tudo sem fórmulas!

Agora nós vamos abrir um arquivo novo do zero, assim nós vamos poder juntar essas informações do outro arquivo sem nem mesmo precisar delas no mesmo arquivo.

Para poder abrir o Power Query e trazer essas informações nós vamos até a guia Dados, depois em Obter Dados > De Arquivo > Da Pasta de Trabalho.

Em seguida você vai selecionar o arquivo onde temos aquelas duas bases de dados para podermos utilizá-las.

Depois de importar os dados você vai notar que tem essa janela com a pré-visualização das informações.

Só que aqui precisamos selecionar as duas bases de dados, então vamos marcar a opção Selecionar vários itens e seleciona as duas tabelas.

Depois disso vamos clicar em Transformar Dados. Isso vai nos levar para o ambiente do Power Query!

Aqui já vamos ter o ambiente Power Query já com as duas tabelas. E vamos selecionar a tabela Cadastro_Vendas.

Depois de abrir o Power Query, nós vamos até a guia Página Inicial e vamos até a ferramenta Mesclar Consultas.

Ao clicar nessa opção será aberta uma nova janela onde vamos fazer a relação entre essas tabelas.

Aqui nós vamos selecionar a outra tabela, que é a Cadastro_Cliente e depois nós vamos selecionar as colunas que possuem o mesmo tipo de informação nas duas tabelas.

Nesse caso é a coluna de código do cliente. Essa informação é que vai nos permitir juntar os dados das duas tabelas.

Isso acontece porque com esse código você vai conseguir na tabela de compras trazer todas as informações desse cliente.

Ao pressionar OK vai ser criada uma coluna, mas ainda não vamos ter as informações trazidas para a tabela.

Para isso você vai clicar nas duas setas que aparecem logo ao lado da coluna Cadastro_Cliente.

Nessa opção nós vamos poder selecionar quais informações da outra tabela nós vamos trazer, então vamos desmarcar apenas o código do cliente, pois já temos essas informações.

E vamos desmarcar a opção de usar o nome da coluna como prefixo.

Com isso você já tem o PROCV dentro do Power Query de forma fácil e rápida!

Agora para deixar nossa base de dados ainda mais completa vamos transformar a coluna de nascimento do cliente para a idade desse cliente.

Para isso você vai selecionar a coluna Nasc_Cliente, depois vamos até a guia Transformar > Data > Idade.

Com isso vamos transformar a data de nascimento em idade, só que ela vai vir em um formato meio diferente, vai vir em dias e não é isso que queremos.

Então basta ir até a opção de Duração e escolher a opção Total de Anos.

Só que ainda temos um problema, temos os anos como números demais, mas geralmente quando falamos em idade só falamos de números inteiros.

Então para finalizar vamos em Arredondamento > Arredondar para baixo.

Com isso teremos de fato a idade desses clientes, para finalizar você pode mudar o cabeçalho para Idade_Cliente, para isso basta dar um duplo clique e mudar o nome.

Agora nós podemos criar uma coluna de classificação de idade para separar em faixas de idade.

Para fazer isso vamos até a guia Adicionar Coluna e vamos clicar em Coluna Condicional.

Essa ferramenta se assemelha a função SE do Excel.

Aqui vamos colocar essas informações para podermos classificar em abaixo de 30 anos, entre 30 e 60 anos e acima de 60 anos.

Com isso vamos ter uma nova coluna já com essa classificação para todas as informações de idade que temos.

Para finalizar podemos ir até a guia Arquivo, depois em Fechar e Carregar.

Feito isso todas essas informações foram passadas para dentro do Excel.

Pronto! Conseguimos fazer o nosso PROCV sem nenhuma fórmula e de forma muito mais fácil utilizando o Power Query.

Outra coisa muito interessante é que esse procedimento é automático, ou seja, se você voltar na sua base de dados e acrescentar novas informações, basta ir até a guia Dados e clicar em atualizar tudo.

Com isso o Excel vai refazer os mesmos procedimentos que você fez para esses novos dados, então vamos ter o cálculo da idade e a faixa etária ou qualquer outra transformação de dados que tenha feito.

Então além de não precisar usar fórmulas você tem um tratamento de dados totalmente automático!

Perguntas frequentes sobre mesclar consultas no Power Query

Conclusão de Mesclar Consultas no Power Query Excel

Nessa aula eu te mostrei como fazer o PROCV no Power Query Excel sem utilizar nenhuma fórmula.

E ainda te mostrei que todos os tratamentos dentro do Power Query são automáticos, então para os dados novos que você inserir eles serão refeitos sem que você precise fazer nada, basta atualizar os dados no Excel!

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!

---

---