Visualizing Merge Join Types in Power BI

Visio Custom Visual (Preview) for Power BI – Quick Look
June 22, 2017
Show all

Visualizing Merge Join Types in Power BI

Visualizing Joins in Power BI using Venn diagrams and Join Diagrams

Over the last couple of years, I have been actively involved in doing Power BI trainings for my clients. Because of that, I am always looking for new and easier ways to explain Power BI concepts to my attendees. Yesterday, I saw a blog post from Reza Rad on Merge Types in Power BI and realized that this is one concept I always have to explain by drawing on a whiteboard during my training sessions. That is when I started thinking – maybe I could create a Power BI report to explain the join types while merging queries. Being able to click through the different join types and seeing the results would definitely make it more easier to understand than me drawing or just talking talking about it. Also, to make it more useful, I wanted to keep the ability to add / modify / delete records in the two tables so that my attendees could see in real time how it will affect the resultant merged tables.

For demonstrating the solution, I made use of 2 simple tables: Table A consists of Customer ID and Customer Name, Table B consists of Customer ID and Email. We will be merging both of these tables using the Customer ID column. In the report, you can see Table A is on the left side, Table B is on the right side and the resultant merged table is on the bottom. We also have a slicer on the top to choose the Join Type, and just under it, we have a description for the Join Type as well as a Venn Diagram. For both Table A and Table B, we have a IsJoined column which denotes whether the corresponding row is present in the Merged table for the selected Join Type. I have embedded the report below, feel free to click and see for yourself (or click on this link to see the full page view).

* The initial version of the report had only the 6 join types available out of the box in Power BI. After sharing this version of the report on Twitter, Imke Feldmann (t | b) said that it would be nice to display the Full Anti Join also, which is not available by default but can be easily added with the help of simple M code (something along the lines of Table.Combine({LeftAntiJoin, RightAntiJoin})). So I added that to the latest version of the report, along with a message that it requires custom code when the Full Anti Join option is selected.‏

 

Now, if you are reading this, most probably you are interested in learning how this was done. To be honest, this report ended up being a little more tricky than I thought it would be and it has some hidden tips and tricks.

– Table A and Table B data is entered through Power BI. So you can add more records and see how the merged table would look like for the changed data. For eg, what happens when I have duplicate customer ids and how will it affect my merged table?

– How does the slicer have images under it? – Chiclet slicer

– How did I make the Venn Diagram to change based on the filter selection? – Use Synoptic Designer to create the Venn diagram, and then link it to your dataset using a DAX measure that will highlight the appropriate area.

– How does the IsJoined column show whether the source table’s record is present in the merged table? – DAX Measure

– How do I display the right results in the Merged table? – A combination of using Power Query and DAX measures.

I know I have answered the questions only on a high level, but if there is enough interest in knowing more about this, please let me know and I might end up writing a follow up for this post detailing all the techniques. Let me know if you have any further questions apart from what I have listed or feedback / bug reports on the same, and I will try to accommodate / fix it as much as I can. Meanwhile, feel free to download the report and play with it yourself. Also, you know where to point the next time someone asks you on the different join types in Merge Queries within Power BI.

I have also published this report to the Power BI Community Data Stories Gallery. Feel free to comment / like / simply interact with the other reports and users out there.

Update

The report has been updated with a second page explaining the join types using Join Diagrams. The join diagrams are inspired from this post – https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/ and was pointed out to me on twitter by @thesqlgrrrl.

14 Comments

  1. Karine says:

    Hey Jason, GREAT work! Wonderful explanation!!! Can I use your report to teach about the subject on my YouTube channel about ? I’ll mention you and tell people to come here and see more! I’m from Brazil and the lesson will be in portuguese.
    Cheers, Karine Lago (MVP)

    • SQLJason says:

      Please feel free to use this work, this is meant for the community :).
      And if possible, send me a link also so that I can see what people are doing with it.

  2. Wanderlei says:

    Angelina and Jen have the same ids…

    • SQLJason says:

      I purposefully kept it in, so that I could let my attendees know what happens when you have duplicate ids or when the key that you use to join have multiple matches. Most of the articles only explain this join with unique ids on both sides, although it is technically possible to have duplicate ids. Feel free to change the data for your use case (after downloading the file) 🙂

      • Wanderlei says:

        I understand, though if the intention is to teach beginners about joins, having ‘bad’ data may not help. Maybe an option to include duplicate ids?

        • SQLJason says:

          It is not bad data necessarily. Consider the Customer ID to be an Account ID. An account can have multiple customers (Joint Account). Also, an account id can have multiple phone numbers or recovery email ids associated with it. That said, the file is available for download, and you can easily change the data as per your audience :)…

  3. Raghu says:

    Jason bhai, Great!!! as usual

  4. […] Visualizing Merge Join Types in Power BI (@SQLJason) […]

  5. Peter says:

    Jason thanks for this, really makes it easy to visualize joins, so easier to explain to others and myself.

  6. Nick says:

    Hi Jason,
    I have downloaded the pbix file through the link above, but it includes only 2 pages, not 3. It’s the first version?

    Thank you

  7. john younie says:

    Dude, this is brilliantly done. I couldn’t download the actual file but I had a look on the BI Community page.

  8. Manohar says:

    Hi Jason.This is a great and very useful article.Thank you so much.I have also posted a question regarding Splitting of the data to your mail.Could you please suggest me on that.

Leave a Reply

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