skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Microsoft 365 + Power BI
    • Dynamics 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Media
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
    • Instructor-led training
    • Getting started
      • Overview
      • Online workshops
      • Self-guided learning
      • Webinars
      • Documentation
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Business intelligence topics
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power BI Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power BI
    • Desktop
    • Service
    • Report Server
    • Power Query
    • Mobile Apps
    • Developer
    • DAX Commands and Tips
    • Custom Visuals Development Discussion
    • Health and Life Sciences
    • Power BI Spanish Community
    • Translated Spanish Desktop
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Training and Consulting
    • Instructor Led Training
    • Galleries
    • Community Connections & How-To Videos
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Events
    • Ideas
    • Custom Visuals Ideas
    • Issues
    • Issues
    • Events
    • Upcoming Events
    • Community Engagement
    • T-Shirt Design Challenge 2023
    • Community Blog
    • Power BI Community Blog
    • Custom Visuals Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Data Stories Gallery
    • Visualizing Merge Join Types in Power BI

    Visualizing Merge Join Types in Power BI

    07-26-2017 19:48 PM

    SqlJason
    Memorable Member
    73990 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    SqlJason
    SqlJason Memorable Member
    Memorable Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    Visualizing Merge Join Types in Power BI

    ‎07-26-2017 07:48 PM

    You can combine two queries in Power BI using the Merge Queries option (in Edit Queries). While merging queries, you will be asked for a join kind. This report visually shows what are the different join kinds as well as show the resulting merged table.

    eyJrIjoiYmNhNzgzYjAtNjc2OS00YjQwLTgwNzItM2Q1MzcyMmJlYjMyIiwidCI6IjZjMGE1YjljLTA4OWEtNDk0ZS1iMDVlLTcxNjEwOTgyOTA0NyIsImMiOjF9

    Preview file
    106 KB
    Labels:
    • Labels:
    • How To
    Message 1 of 10
    73,990 Views
    55
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-15-2019 10:23 AM

    Hi Jason,

    I expanded on your sample and I notice that when you do Cross Join it show correct value in power query (5x4=20)
    But when you try to show in power bi table visual you get only 15 records (5x3) ?
    How do you correct that? see attachments

    Thanks,

    Oded Dror

    Preview file
    71 KB
    Preview file
    129 KB
    Message 9 of 10
    55,884 Views
    0
    Reply
    Anonymous
    Not applicable
    In response to Anonymous
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎11-15-2019 12:06 PM

    Jason,

    Please Discard the previous question I fix it

     

    Thanks,

    Oded Dror

     

    Message 10 of 10
    55,867 Views
    0
    Reply
    maguilarBI
    maguilarBI Resolver II
    Resolver II
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎09-12-2019 10:08 AM

    Good Job!

    Message 8 of 10
    57,919 Views
    0
    Reply
    ketan10
    ketan10 Resolver I
    Resolver I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-28-2017 10:24 PM

    This is pretty interesting @SqlJason ! Good work.

    Message 7 of 10
    73,845 Views
    1
    Reply
    deldersveld
    deldersveld
    MVP
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-26-2017 08:23 PM

    Are you using the Visio custom visual for the main join diagram?

    Message 2 of 10
    73,984 Views
    0
    Reply
    SqlJason
    SqlJason Memorable Member
    Memorable Member
    In response to deldersveld
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-26-2017 08:52 PM

    Synoptic Panel. Just made a simple png using PowerPoint and then used the Synoptic Designer to create the 3 areas within the Venn chart.

     

    I initially thought of using the Visio custom visual, but then remembered that you will need to make the visio file public with everyone to publish this report to the web. This visual requires One Drive for Business (so I don't have a personal account) and right now, my company does not allow sharing files publicly. So could not use the Visio option.

    Message 3 of 10
    73,975 Views
    2
    Reply
    deldersveld
    deldersveld
    MVP
    In response to SqlJason
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-27-2017 06:14 AM

    Thanks. I see Synoptic's zoom control now. I haven't spent any time with the Visio preview.

    Message 4 of 10
    73,938 Views
    0
    Reply
    leonardmurphy
    leonardmurphy Skilled Sharer
    Skilled Sharer
    In response to deldersveld
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-27-2017 11:46 AM

    Nice work Jason (and great idea too)!

     

    I haven't seen anything explaining joins as simple to understand as your diagram on page 2. As much as the Venn diagram is a classic, the blocks were as straightforward as side-ways Tetris to me (seeing which blocks "fall" to the right-hand column based on their presence and/or absence in the other 2 columns).

     

    Cross joins might be worth adding to the mix (but that would not be fun to do).

    ---
    In Wisconsin? Join the Madison Power BI User Group.
    Message 5 of 10
    73,912 Views
    0
    Reply
    SqlJason
    SqlJason Memorable Member
    Memorable Member
    In response to leonardmurphy
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎07-27-2017 12:38 PM

    Thanks Leonard, though I can only take the credit for developing the actual solution. 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. 

    Message 6 of 10
    73,909 Views
    2
    Reply

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    © 2023 Microsoft

    Follow Power BI

    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks
    Consumer Privacy Act (CCPA) Opt-Out Icon Your Privacy Choices