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.
Cada um dos intervalos deve ser adicionado ao Power Query.
Colocando o cursor sobre um dos intervalos acedemos ao menu Dados [Data] – De Tabela/Intervalo [From Table / Range]
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.
E repetimos o mesmo processo para o segundo conjunto de dados…
Fechar e Carregar para… Apenas Criar Ligação.
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].
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.
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.
As duas consultas juntam-se e efetuando um clique no botão de expandir…
E podemos selecionar as colunas que são retornadas da segunda tabela.
A consulta final pode ser carregada depois para o Excel, agora sobre a forma de uma tabela.
Escolhemos o local onde carregar os dados.
O resultado final é apresentado na imagem em baixo.
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.
Para a nossa consulta, repetimos o processo de junção, mas escolhemos a opção Externa à Direita [Rigth Outer] Join.
Neste caso é possível ver os registos da tabela da direita que não têm correspondência à esquerda.
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:
Conseguimos observar as várias linhas de ambas as tabelas.
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…
E executa-se da mesma forma, optando pelo Tipo de Associação: Interna [Inner Join]
O resultado da consulta é o seguinte depois de expandir os campos pretendidos…
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…
Repetimos o processo de junção das queries onde escolhemos o Tipo de Associação Anti à Esquerda [Left Anti].
O resultado da consulta é o apresentado, após expandir as colunas e remover os campos desnecessários.
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.
Seguindo o mesmo padrão de junção das consultas anteriores, procedemos à escolha do Tipo de Associação: Anti à Direita [Right Anti]
O Resultado da consulta mostra apenas os registos da tabela à direita “Produtos”.
Outros conteúdos relevantes:
Power BI: Cálculos Visuais: Funções COLLAPSE e EXPAND
Neste novo vídeo, vou mostrar-te as restantes novas funções DAX, disponíveis para os cálculos visuais! Vamos lá?
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...