Power Query: Vários tipos de junção (Join Kinds) - Exceldriven

Power Query: Como utilizar os vários tipos de junção (Join Kinds)?

Neste artigo 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. Vamos lá?

O Power Query é bastante rico! Neste caso, se pretendemos analisar se uma ou várias colunas coincidem entre 2 tabelas, podemos retirar a necessidade de utilizar a função CORRESP [MATCH], para analisar se o valor de uma determinada célula existe num intervalo.

Para além desta “validação”, naturalmente, o Power Query tem a vantagem de poder retornar todas as colunas de uma tabela que correspondam ou não ao valor coincidente para a nossa pesquisa.

Estas opções são definidas através da opção Intercalar Consultas [Merge Queries], que nos disponibilizam 6 possibilidades para juntar as colunas de 2 tabelas. Deste modo, excluímos a necessidade de utilizar funções como PROCV [VLOOKUP], ÍNDICE [INDEX] e a já referida CORRESP [MATCH].

Vamos então analisar como podemos utilizar estas ferramentas.



Passo 1 – Criar as queries de base

As queries de base, são criadas convertendo os dados para tabela e adicionando-os ao Power Query, que depois retornam ao Excel sobre a forma de uma ligação apenas. Estas consultas normalmente são designadas como “Staging Queries” que funcionam como consultas num estado de Base para criação da consulta final.

Começamos então com 2 intervalos que necessitamos de coincidir.

img_01.jpg

Cada um dos intervalos deve ser adicionado ao Power Query.

img_02.jpg

Colocando o cursor sobre um dos intervalos acedemos ao menu Dados [Data] – De Tabela/Intervalo [From Table / Range]

img_03.jpg

Já no Power Query, modificamos o nome da consulta à escolha do utilizador e carregamos a informação, apenas como uma ligação aos dados originais.

img_04.jpg

E repetimos o mesmo processo para o segundo conjunto de dados…

img_05.jpg

Fechar e Carregar para… Apenas Criar Ligação.

img_06.jpg

Neste momento temos as duas consultas de base “Staging Queries”.

Passo 2 – Juntar as 2 consultas de base numa consulta “final”

Para juntar as consultas podemos aceder ao Painel de consultas e Ligações, que caso não esteja visível é possível ativar pelo comando Dados [Data] – Consultas e Ligações [Queries and Connections].

img_02_1.jpg

De seguida utilizando uma das consultas (Armazém) podemos iniciar o processo de junção com outra consulta. Este método irá criar uma nova consulta.

img_08.jpg

Junção Externa à Esquerda [Left Outer]

Na junção definimos as tabelas a juntar, e a coluna comum entre ambas [ID], definindo depois a opção Externa à Esquerda [Left Outer] que retorna todos os registos da tabela à esquerda, e os registos que correspondem da tabela da direita.

join_01.jpg

img_09.5.jpg

As duas consultas juntam-se e efetuando um clique no botão de expandir…

img_09.jpg

E podemos selecionar as colunas que são retornadas da segunda tabela.

img_10.jpg

A consulta final pode ser carregada depois para o Excel, agora sobre a forma de uma tabela.

img_14.jpg

Escolhemos o local onde carregar os dados.

img_16.jpg

O resultado final é apresentado na imagem em baixo.

img_16_2.jpg

Outros tipos de junções.

O processo agora é semelhante para testar outros tipos de junções. Em que cada tipo de junção devolve um conjunto de registos diferente.

Junção Externa à Direita [Right Outer]

Esta junção é o “oposto” da primeira, que neste caso retorna todos os registos da segunda tabela e os respetivos registos coincidentes da primeira tabela, representada deste modo.

join_02.jpg

Para a nossa consulta, repetimos o processo de junção, mas escolhemos a opção Externa à Direita [Rigth Outer] Join.

img_18.jpg

Neste caso é possível ver os registos da tabela da direita que não têm correspondência à esquerda.

img_19.jpg

Retornar todos os registos de ambas as tabelas: Externa Completa [Full Outer]

Este tipo de junção é interessante quanto pretendemos ter uma vista geral e numa única tabela de todos os registos de ambos os lados, não só os coincidentes, mas também aqueles que não correspondem.

Repetimos o processo da mesma forma, gerando uma nova consulta através de uma das anteriores (Armazém) no caso. E escolhemos o Tipo de Associação: Externa Completa [Full Outer] que é representada por este esquema:

join_03.jpg

img_21.jpg

Conseguimos observar as várias linhas de ambas as tabelas.

img_22.jpg

Retornar apenas os registos coincidentes de ambas as tabelas: Interna [Inner Join]

Este tipo de junção mostra apenas os registos (linhas) que coincidem em ambas as tabelas. É também um tipo de junção bastante útil, pois é aquele que nos indica numa tabela final, os valores em que ambos os registos das tabelas correspondem.

A junção é representada com o seguinte esquema…

join_04.jpg

E executa-se da mesma forma, optando pelo Tipo de Associação: Interna [Inner Join]

img_23.jpg

O resultado da consulta é o seguinte depois de expandir os campos pretendidos…

img_24_1.jpg

img_24.jpg

Retornar apenas os da tabela da esquerda que não têm correspondente “à Direita” - Anti à Esquerda [Left Anti]

Nestes próximos exemplos o objetivo passa por obter os registos que não coincidem, em vez de analisar os registos que correspondem. Estas últimas associações são extremamente úteis, pois normalmente quando encontramos um registo com esta característica, será para o eliminar. Assim vamos verificar os registos da tabela da esquerda que não têm correspondência na tabela da direita.

O esquema é representado na seguinte imagem…

join_05.jpg

Repetimos o processo de junção das queries onde escolhemos o Tipo de Associação Anti à Esquerda [Left Anti].

img_26.jpg

O resultado da consulta é o apresentado, após expandir as colunas e remover os campos desnecessários.

img_27.jpg

Associação Anti à Direita [Right Anti]

Este último tipo de associação retorna apenas os registos da segunda tabela (direita) que não têm correspondência com a tabela da esquerda. O esquema pode ser representado da seguinte forma.

join_06.jpg

Seguindo o mesmo padrão de junção das consultas anteriores, procedemos à escolha do Tipo de Associação: Anti à Direita [Right Anti]

img_29.jpg

O Resultado da consulta mostra apenas os registos da tabela à direita “Produtos”.

img_30.jpg


Outros conteúdos relevantes:

INDEX vs OFFSET - Duas funções extremamente úteis! - Parte 2

INDEX vs OFFSET - Duas funções extremamente úteis! - Parte 2

Na segunda parte do tutorial, e como prometido, iremos continuar a demonstrar as potencialidades destas funções. Nomeadamente...

Continuar a ler...

Microsoft Excel – Aprenda a analisar vários resultados com a Gestão de Cenários

Microsoft Excel – Aprenda a analisar vários resultados com a Gestão de Cenários

Neste tutorial, utilizamos a ferramenta de gestão de cenários, do Microsoft Excel, para ajudar na análise de vários cenários e na tomada de decisão.

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

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 -

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

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

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 -

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 -

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

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

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