Power BI Merge Queries ou Joins

Não confundir essa função aqui com o Merge de colunas de texto. Esse MERGE aqui é diferente.

MERGE QUERIES no power bi é um tipo de JOIN, se vc já fez join de múltiplas tabelas usando linguagem SQL sabe do que eu estou falando. MERGE também pode ser pensado com um tipo de VLOOKUP, mas assim, o importante é vc aprender o MERGE mesmo, então continua lendo que vc vai entender.

Veja a tela de opção ao se clicar em merge:

A tabela fVendas é a tabela que estou selecionado, então quando eu clico em MERGE ele já me traz essa tabela como a primeira tabela. Também podemos chamar essa tabela de “Tabela à esquerda”. Esse negócio de tabela à esquerda é importante pra vc saber de que lado o JOIN vai acontecer.

Antes deu falar como os joins funcionam, deixa eu te falar quais são os possíveis JOINS que o MERGE disponibiliza no Power BI, são eles:

LEFT OUTER (all from first, matching from second)

RIGHT OUTER (all from second, matching from first)

FULL OUTER (all rows from both)

INNER (only matching rows)

LEFT ANTI (rows only in first)

RIGHT ANTI (rows only in second)

Antes deu explicar cada um desses JOINS, dá uma olhada nessa imagem e perde um tempo aqui tentando entender os resultados, porque isso é muito importante. Abaixo eu explico cada um deles de forma rápida.

Se liga! É importante que vc saiba que é possível fazer JOINS usando uma ou mais colunas.

Então, presta atenção… Nos relacionamentos lá do power bi não é possível relacionar tabelas usando mais de uma coluna, ou seja, no power bi, relacionamento entre tabelas só acontece de 1 pra 1 coluna.

Porém, no Power Query é possível fazer joins usando mais de uma coluna. Ficou claro? Se não tiver ficado claro, vai lendo, que mais pra frente eu falo sobre JOINS com várias colunas, blz?

LEFT OUTER

Left outer, ou LEFT JOIN, são a mesma coisa… Então o que acontece é o seguinte; quando vc usa um MERGE tipo LEFT OUTER o resultado vai ser todos itens da tabela da esquerda e mais os itens da tabela da direita, desde que os itens da direita sejam encontrados nos itens da esquerda. Se não entendeu, continue lendo.

No meu exemplo, lá na primeira imagem do post, eu estava com a tabela de fVENDAS selecionada, logo minha tabela da esquerda. Se eu selecionar minha outra tabela sendo a tabela de Metas, logo Metas será minha tabela da direita… Então eu tenho que selecionar em qual coluna, ou colunas, essa ligação será feita.

Se eu selecionar colunas nada a ver uma com a outra, então só vai retornar null como resultado da tabela da direita, porque não vai achar nada. Olhar só:

Eu selecionei NFE como coluna da esquerda e cdVendedor como coluna da direita.

O Power BI não encontrou nada, viu ali no final o tickzinho verde falando ZERO matches? Então… se eu der OK aqui, vai gerar uma tabela igual a tabela da esquerda e com todos campos da tabela da direita, só que os campos tudo em null, porque não deu match.

Olha só:

A tabela resultante foi essa, daí se eu clicar pra expandir o resultado de METAS, vc vai ver que deu tudo null.

Então serviu pra nada neh…

O que eu preciso é fazer um JOIN, ou um MERGE, que me resulte alguma coisa que eu precise.

Então outro exemplo de LEFT JOIN, que agora vai dar bom.

Vou pegar as mesmas tabelas, fVendas e Metas. Só que o join agora vai ser na coluna de cdVendedor. Olha só como já apareceu match:

Praticamente todas linhas deram match. Isso porque na tabela de fVendas tem vendedor que não existe na tabela de Metas. Mas eu explico melhor daqui a pouco, primeiro vamos ver como ficou o resultado.

Perceba que agora não deu mais tudo null, agora retornou um monte de dados.

Beleza, já entendemos o conceito, vamos fazer mais um exercício de LEFT JOIN, ou LEFT OUTER JOIN, que é a mesma coisa… Olha só… vamos pegar essa mesma combinação que fizemos, de fVendas com Metas, com left join na coluna de cdVendedor. Só que, do resultado, eu vou remover todas as colunas, vou deixar apenas 2 colunas, a coluna cdVendedor da tabela fVendas e a cdVendedor da tabela Metas. Vamos ver o resultado.

Beleza, deu uma porrada de linhas aí mostrando esse relacionamento. Lembrando que o relacionamento do LEFT JOIN é: tudo que tem na tabela da esquerda e trazendo tudo da tabela da direita que deu match com a da esquerda. Então assim, se existir um cdVendedor na tabela da esquerda (fVendas) e que esse cdVendedor não exista na tabela da direita (Metas), então em algum lugar na coluna de Metas.cdVendedor, vai estar null.

Pra ficar mais fácil de conseguir visualizar essa questão do null vamos remover os duplicados. Olha só o resultado:

Veja que tivemos 12 resultados, porque na tabela da esquerda (fVendas) temos 12 registros únicos na coluna cdVendedor, porém… na tabela de Metas, que é nossa tabela da direita, temos apenas 11 registros para o cdVendedor, logo um dos registros vai dar null, porque simplesmente não tem match da esquerda para a direita.

Na imagem a linha 5, cdVendedor 183 existe apenas na tabela da esquerda (fVendas). Como não tem registro compatível na tabela da direita, então retornou null.

Até agora foi tudo bem básico, mas vamos dificultar um pouquinho…

No LEFT OUTER JOIN você pode ter um comportamento de repetir registros caso a tabela da direita tenha mais de um registro para cada registro da tabela da esquerda.

Então pensa assim, se na sua tabela da esquerda (fVendas) vc tem um cdVendedor código 102 com 3 registros, e na sua tabela da direita vc tem lá esse mesmo código 102 com também 3 registros, então se vc fizer o LEFT JOIN ele vai trazer 9 registros, porque são 1 registros na esquerda, pra cada 3 registros na direita, logo, 3×3 = 9.

Olha só esse LEFT JOIN de fVenda com Meta nesse exemplo de 3 por 3.

De cara ele diz que achou 3 matches de 3. Daí você vai ver que recebeu o resultado assim:

Se você expandir a tabela da direita, Metas, e pegar todos os campos, vai acontecer o seguinte:

O que eram 3 registros agora são 9, se liguem na columa Metas.Mes e Metas.ValorMeta, essas colunas tem valores distintos… Então como existem esses 3 registros na tabela da direita, na hora que vc fizer o LEFT JOIN ele vai retornar tudo que bater com os dados da esquerda. Muito cuidado com isso, sua tabela pode acabar com linhas indesejáveis.

Aqui nesse link vc pode brincar melhor com os JOINS e entender melhor do que está acontecendo https://community.powerbi.com/t5/Data-Stories-Gallery/Visualizing-Merge-Join-Types-in-Power-BI/td-p/219906.

Pra evitar esse tipo de linhas repetidas, o que vc precisa fazer é criar uma chave de ligação composta, ou seja, fazer um Join usando mais de uma coluna, como explico abaixo.

JOINS COM MAIS DE UMA COLUNA

Antes de seguir pra os outros tipos de MERGES (ou JOINS), deixa eu aproveitar que agora que vc dominou o LEFT JOIN pra te explicar esse lance de MERGE usando mais de uma coluna.

Seguinte, quem explica isso é o radacad nesse link https://radacad.com/relationship-in-power-bi-with-multiple-columns.

Mas eu vou tentar explicar aqui do meu jeito…

Pensa no seguinte, vc tem duas tabelas, a tabela de BUDGET (orçamento em inglês) e a tabela de dimData.

Então, o que vc quer é criar um relacionamento entre a tabela de Budget e de dimData lá no Power BI, só que no Power BI, naquela parte de relacionamento, só tem como vc relacionar tabelas usando apenas 1 coluna com 1 coluna, e na sua tabela de Budget vc não tem nenhuma coluna que realmente identifique o seu registro. E agora? Como que resolve isso?

Então se liga nessas imagens:

Essa acima é sua tabela de Budget, ela tem Year, Month e Budget Amount.

E abaixo a sua tabela de Data, ou dimData, é uma tabela de Data que tem uma estrutura assim:

Então, se vc aplicar aqui seus conhecimentos de LEFT JOIN, vc vai ver que, se fizermos um LEFT JOIN de Budget com dimData usando Year ou Month não vai sair nada que preste, porque não vai gerar nenhuma informação útil pra ser usada lá no relacionamento que queremos criar (vai gerar uma muitos pra muitos, eca).

Pra resolver isso o pulo do gato está em criar uma coluna de DAY na tabela Budget e pronto, tá quase resolvido nosso problema, porque o seguinte… Quando vc criar a coluna DAY no Budget vai ver que na tabela dimData tem a mesma estrutura de Year, Month e Day, daí aqui vc pode fazer um MERGE entre essas duas tabelas, que o resultado vai ficar assim, olha:

Nessa primeira imagem eu selecione as tabelas que eu quero e selecionei os campos na ordem que eu quero.
Pra selecionar os campos na ordem basta usar Control + click.

Veja que deu 6 de 6 resultados, ou seja, deu Match.

A tabela resultante vai ficar assim:

Daí, se vc expandir a coluna dimData pode pegar todos campos que quiser. No meu caso, vou pegar só o campo de DateKey, porque é o campo que eu preciso pra fazer meu relacionamento. O resultado é esse:

Agora é só ir nos relacionamentos e fazer o relacionamento no Power BI!

O Merge2 é a tabela que criamos (eu dei um Merge as New, daí ele cria uma tabela nova com o resultado). A tabela Budget só tá ali pra te mostrar a tabela original, mas o que vc precisa mesmo é o Merge2 e a dimData, porém vc não pode deletar a Budget porque ela está sendo referenciada na Merge2, pra evitar isso é só dar um Merge ao invés de Merge as New, que aí tu fica só com uma entidade (tabela).

Apenas Merge (sem merge as new)

RIGHT OUTER

Agora que vc já entendeu LEFT JOIN e como fazer JOIN usando mais de uma coluna, vou tentar explicar de forma mais rápidas os outros joins.

No RIGHT OUTER ou RIGHT JOIN o conceito é o seguinte; o merge vai ser feito mas vai trazer apenas os itens da direita e da esquerda vai trazer apenas os itens que coindirem com os da direita, é a mesma coisa que o LEFT JOIN ao contrário.

Inclusive, pra provar que é um LEFT JOIN ao contrário, eu vou pegar o exemplo que usei no LEFT JOIN e vou trocar a posição das colunas, o que era tabela da esquerda vai virar tabela da direita e vice-versa. Então no exemplo do left join a tabela da esquerda era a tabela fVendas e da direita era Metas. Pra esse exemplo, vou inverter, vou usar tabela da esquerda METAS e tabela da direita fVendas. O resultado tem que ser o mesmo, vamos ver!?

De cara, já deu o mesmo resultado de matches. Vamos ver se vai dar certo mesmo no final.

Pra adiantar, eu fiz o seguinte, dei um Merge as New e desse resultado explodi o campo que veio no join da tabela fVendas. Desse resultado peguei fVendas.cdVendedor e removi as outras colunas deixando apenas essa coluna e a coluna cdVendedor da tabela Metas. O resultado foi:

Pronto, o mesmo resultado do LEFT JOIN, só que aqui no RIGHT JOIN ficou ao contrário.

Bem, com esse exemplo ficou fácil, mas vou fazer mais um exemplo pra ficar mais claro.

Dessa vez eu vou fazer o seguinte, vou pegar a tabela de fVendas na esquerda e Metas na direita e vou aplicar um Right Join, o que deu é isso aqui:

De cara já deu pra ver que os matches foram apenas 264, porque na tabela de Metas, que é a tabela da direita, temos apenas 264 registros.

O resultado foi a tabela de fVendas inteira, menos os registros que não existem no RIGHT JOIN, porque todos registros de Metas existem na tabela de fVendas. Ou seja, todos 11 vendedores de Metas estão contidos em fVendas, porém em fVendas temos aquele vendedor que não existe nas Metas, lembra dele?

O cdVendedor 183 existe apenas na fVendas, então se vc procurar você não vai achar nenhum registro nessa tabela resultante desse RIGHT JOIN, porque a tabela da direita é Metas e nela o cdVendedor 183 não existe. Se liga:

Ou seja, nenhuma das 7033 linhas que pertencem ao cdVendedor 183 da tabela fVendas aparecem nessa tabela resultante desse RIGHT JOIN.

FULL OUTER JOIN

FULL OUTER traz todos resultados de tanto da tabela da esquerda, quanto da direita.

Então considere o seguinte cenário:

Na tabela da esquerda, fVendas, temos 3 registros para o cdVendedor 102.

Na tabela da direita, Metas, temos 6 registros, 3 para os cdVendedor 102 e 3 para o cdVendedor 265.

Revisando:

Nesse cenário, se eu fizer um LEFT OUTER JOIN o resultado vai ser 9 registros para o vendedor 102 (isso já vimos lá no estudo de LEFT JOIN).

Se for um RIGHT OUTER JOIN o resultado vai ser 12 registros, 9 do vendedor 102 e mais 3 do 265 (isso já vimos no estudo de RIGHT OUTER).

Coincidentemente, se aplicarmos o FULL OUTER aqui, o resultado também vão ser 12 registros, igualzinho o RIGHT OUTER, mas por quê? Porque no exemplo que estamos usando não existem registros na fVendas de um vendendor diferente do 102 e 265.

Então vamos adicionar um vendedor 144 na tabela fVendas e usar o FULL OUTER, o resultado vai ficar assim:

São 16 linhas dessa vez, porque agora existem mais 4 registros na fVendas relacionados ao cdVendedor 144. Essas linhas NULL na imagem acima são exatamente os registros do 144, se chegar a imagem pra esquerda vc vai ver que na coluna cdVendedor de fVendas vai estar 144.

Resumindo é o seguinte, FULL OUTER vai trazer tudo de tudo.

INNER JOIN

INNER JOIN mostra apenas os registros que existam tanto na esquerda quanto na direita.

Então usando esse mesmo exemplo do FULL OUTER dos vendedores 102, 265 e 144, qual vc acha que seria o resultado se aplicarmos um INNER JOIN???

Vamos pensar assim, estamos conectando uma tabela na outra via cdVendedor, e na fVendas existe os vendedores 102 e 144, enquanto que na Metas existem o 102 e 265… logo… se o INNER JOIN precisa de que um match perfeito, apenas o vendedor 102 vai ter seus registros retornados… porque é o único presente nas duas tabelas.

Vamos ver como que ficou:

Veja que trouxe mesmo só os registros do 102, mas lembrando… Como existem 3 registros do 102 na tabela de fVendas e mais 3 registros do 102 na tabela de Metas, o resultado será 3 registros de Metas pra cada registro de fVendas, logo 3×3 = 9 registros.

LEFT ANTI e RIGHT ANTI

Esses MERGES ou JOINS do tipo ANTI eu nunca usei muito não pra ser sincero, mas é o seguinte, ele pega tudo que NÃO combinar com o outro lado, ou seja, se for LEFT ANTI vai pegar tudo que está na tabela da esquerda e que não tem matches na tabela da direita, agora, se for RIGHT ANTIG vai pegar tudo que está na tabela da direita e que não tem matches na tabela da esquerda.

Então no nosso cenário, considerando que existem na tabela da esquerda, que é nossa fVendas os vendedores 102 e 144 e que na tabela da direita temos os vendedores 102 e 265, então se :

Eu fizer um LEFT ANTI nesse cenário, vai me retornar apenas registros da tabela da esquerda que não tem match na direta, ou seja, apenas registros do vendedor 144.

Agora, se eu fizer um RIGHT ANTI nesse cenário, vai me retornar apenas registros da tabela da direita que não tem match na tabela da esquerda, ou seja, vao me retornar apenas registros do vendedor 265.

Vale lembrar que, quando vc usa LEFT ANTI o resultado que vem da tabela da direita vem tudo null, e quando vc usa RIGHT ANTI os resultados da tabela da esquerda vêm tudo null. Então olha só:

Resultado do LEFT ANTI: