Types of join/merge available in Power BI/ Excel


Hello and good evening this is Ruth Pozuelo from Curbal.com and today I want to talk about the different types of
table joints that you can do in Power BI. I’m going to use excel for the demo
but of course the same types are available in Power BI too. If we go to our
excel file, the reason i did it in excel is because i have made this kind of
template of what every type of join is and how it behaves and it would
have been a little bit trickier to do it in Power Bi Desktop, but again it is the
same concept. What we’re going to do is when you try to merge two
tables, here is an example, you get a few types or
joins you can do: left outer, right outer, full outer etc and the
explanation here helps, but sometimes you have to think …or at least I have to
think twice: what was this join doing? What I did, was to create a little chart
that explains what every type of join does and we’re going to go through them today. If we start with the left outer: what we have here is Table A and then
we have Table B, those are cells that has data ok? and the green represents cells that match, so green in Table A matches green in Table B. I have a real
example here: this is TableA and this is Table B and the green ones
are the ones that match, ok? If we do a Left Outer, what we are actually doing is:
we’re taking all the columns from A and taking the matching ones from B. In our example here, if we do a Left Outer, what we will get is all
the columns from A and then the matching ones from B. You will get this file so you don’t have
to worry about the reproducing it, you cannot load this from the description box. If we go to the next one, the Right Outer, what it does is: it gets all the rows from
B and the matching rows from A, so all the rows from B and the matching ones
from A. If we check our example: here it takes all the column from B and the matching ones from A. Then we have a Full Outer and what this does is it takes all
the rows, so in this case, it takes all the rows
from A and all the rows from B and of course, matches the ones that matches. The Inner join, it just takes the matching rows, so if we look at our example only
the matching rows will appear. Then we have Left Anti, and what it does is it takes all
the rows from A that does not match B, so it removes the green ones. If we go
here to Left Anti, don’t get any from B, but we get all from A. And
of course Right Anti will do the exact opposite: will take all the rows
from B and remove the matching ones from A and we
can see the example here: nothing from A but all the columns from B. It is
that simple and complicated 🙂 You can download the file and then you can use
this graph as a reference it will help you if you forget what does what
and of course every type of join has a different use. In this
example, if we would say that here is a table of products that are
manufactured in a factory and here’s a table of where they are stored, all the
different joints would give us different data. The Left Outer, for example will
give us what products are in stock but we will get the entire list of products so we have all the products that we
produce and the producta that are in stock and here we could have the stock value for
example, like 38 or 48 so you can do a merge to get that information.
Right Outer, what we will get is all the stock products and it
will give us information about the products that we manufacture, if we need that. Full Outer will give a complete list of products, the Inner join, will give a list of
only the products that we manufacture and have in stock and then Left Anti will give us a
list of all the products that we don’t have in stock and Right Anti will give us a
list of all the products that we have in stock but we don’t manufacture. There’s different needs for the different type of joins, you have to
decide from the beginning which one you would like to use. I hope you enjoyed
this video, you have the file for download available if you want to use it. If you liked this video, let me know by liking it, if you have any comments or suggestions for
videos, questions just let me know in the comment box or any of the social media
channels that are listed on the description box and subscribe I
published the around a video a week. Have a nice evening! byeeee

23 thoughts on “Types of join/merge available in Power BI/ Excel

  1. Buenas tardes Ruth, yo preguntando de nuevo, le cuento que sus videos me han servido mucho y me han iniciado en mi formación, te cuento mi perfil, soy Ing en Sistemas y ademas trabajé durante 7 años en análisis de ventas comercial, actualmente estoy sin trabajo pero aprendiendo. Resumiendo me gustaría hacerte una consulta muy puntual con respecto a algo que estoy practicando, no es para que me lo hagas sino para que me oriente que debo hacer. Pregunta existe problema si te envió un correo con la consulta la lees y no te quito mas de 10 min de su vida, la intencion del correo es para no llenarle de spam el canal?.

    Muchas gracias Ruth

  2. thanks for the great explanation, the diagram speaks for itself!
    only one minor thing: you misplaces bottom green cell in output table in Left outer, it shall be one row lower

  3. Hi, I used LEFT OUTER JOIN in my report but scheduled refresh functionality on power bi server doesn't support this apparenlty…
    Is there another function (DAX) I can use to get same result. Comparable to the UNION function wich is the same as the APPEND query.
    thanks a lot!

  4. as always, simple and good explanation! i would like to know, is it possible to make Full Outer Join of 2 tables if both of the tables have duplicated items? many thanks for any feedback

  5. Thanks Ruth very clear explanation, i wanted to download your example file but received a scary message "Your connection is not private", just to let you know.

  6. Hello Ruth,

    I have a question, please help.

    I have 2 separate tabular cubes which I want to pull in Power BI, some of the dimensions/keys are common in these 2 cubes. Is it possible to bring the 2 cubes in power bi in one single report? if so how?

    Thank you for all your lovely videos.

Leave a Reply

Your email address will not be published. Required fields are marked *