Power Bi Join Types

Hello Is it possible with power query to use Table.Group with a min or max as an aggregated column and also having some other columns associated to the result of the min or max? For example if I have: First Name Last Name Age Job Marie.

1. Power Bi Left Outer Join
2. Power Bi Video Tutorial

Power Bi Left Outer Join

1. Group by is a type of query which is also used in power bi, there is an editor in power bi which is known as query editor window which has various queries for accessing data and such query is Group by which is used to group rows based on a given certain value.
2. This post covers one of my favorite Power Query topics: the self join! I like using this technique to calculate changes over time, which I will demonstrate with the example below. A self-join merges a query with itself, which allows for a row to gain additional information from different rows in the same query.

Last week's blog post on merging tables using Outer joins has proved to be pretty popular. (I guess I'm not the only one who struggled with this concept!) This week we'll look at the remaining three options, showing how to merge tables using Inner and Anti joins.

Again, we have three join types to explore this week:

• Inner Join
• Left Anti Join
• Right Anti Join

If you read last week's article you may already have an idea of what you'll be seeing here, but we'll make sure we go through it in full anyway. (If you HAVEN'T read last week's article, you might want to do so, as this one just builds on steps that readers will already be comfortable with.)

We're going to work with the same set of data as we did last week, although we have a different sample file for it (to hold the completed queries.) That file can be downloaded here.

As a reminder, here's what we're set up with:

As I explained last week:

… two tables of data, one called Transactions, and one called ChartOfAccounts.

Now, the key piece you want to watch here is that we need the Account-Dept combination to exist in both tables in order to make a perfect join between them. Think VLOOKUP for a second… if you tried to make a VLOOKUP for account 10045 from the Transactions table against the ChartOfAccounts table, what would you get? Of course, you'd get #N/A since 10045 doesn't exist in the ChartOfAccounts table.

In this case we have items in both tables that don't exist in the other. (The yellow records in the Transactions table don't have a match in the ChartOfAccounts table, and the red records in ChartOfAccounts don't have a match in the Transactions table.) With these differences we can test how each of the first three join types available to us behave when we try to merge the data in both tables together.

We've already got the ChartOfAccounts query and Transactions queries set up as connection only queries, so we're ready to jump right into comparing the join types.

This join type stands out somewhat from the others in that there is no 'left' or 'right' version. Let's build the join to explore why that is:

• Open the Workbook Queries pane
• Right click the Transactions query and choose Merge
• Select ChartOfAccounts for the bottom table
• For the top query (Transactions) select Account, hold down CTRL and select Dept
• For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
• Change the Join type to 'Inner (only matching rows)'
• Click OK

Like last week, the data lands in Power Query, and we'll take the following steps to expand the rows:

• Right click the NewColumn column –> Rename –> COA
• Click the Expand icon on the top right of the COA column
• Leave all the defaults and click OK

We're going to work with the same set of data as we did last week, although we have a different sample file for it (to hold the completed queries.) That file can be downloaded here.

As a reminder, here's what we're set up with:

As I explained last week:

… two tables of data, one called Transactions, and one called ChartOfAccounts.

Now, the key piece you want to watch here is that we need the Account-Dept combination to exist in both tables in order to make a perfect join between them. Think VLOOKUP for a second… if you tried to make a VLOOKUP for account 10045 from the Transactions table against the ChartOfAccounts table, what would you get? Of course, you'd get #N/A since 10045 doesn't exist in the ChartOfAccounts table.

In this case we have items in both tables that don't exist in the other. (The yellow records in the Transactions table don't have a match in the ChartOfAccounts table, and the red records in ChartOfAccounts don't have a match in the Transactions table.) With these differences we can test how each of the first three join types available to us behave when we try to merge the data in both tables together.

We've already got the ChartOfAccounts query and Transactions queries set up as connection only queries, so we're ready to jump right into comparing the join types.

This join type stands out somewhat from the others in that there is no 'left' or 'right' version. Let's build the join to explore why that is:

• Open the Workbook Queries pane
• Right click the Transactions query and choose Merge
• Select ChartOfAccounts for the bottom table
• For the top query (Transactions) select Account, hold down CTRL and select Dept
• For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
• Change the Join type to 'Inner (only matching rows)'
• Click OK

Like last week, the data lands in Power Query, and we'll take the following steps to expand the rows:

• Right click the NewColumn column –> Rename –> COA
• Click the Expand icon on the top right of the COA column
• Leave all the defaults and click OK

The results of these steps are shown here:

We can now see the meaning of the Inner Join. Unlike the Full Outer Join that pulls in all records from both tables whether there is a match or not, the Inner Join only pulls in rows that exist in both the left and right tables. In other words… all those red and yellow rows shown in our original data set? They're missing from this output.

I can see this being very useful for indentifying matching records, giving a list to show which ones matched without polluting the data set with non-matching items.

Let's finalize this query and move to the Anti Joins:

• Change the name of the query to Inner
• Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

Now that we've seen Outer and Inner joins, give some thought as to what an Anti join might do…

Done? Let's go see if you're right.

Go to Get Teams for free and choose the Sign up for free button. If you don't see the Sign up for free button, scroll down (nearly to the bottom of the page) to Get Microsoft Teams for your organization today, and then select Sign up for free. Enter the email address you want to. Go to products.office.com/microsoft-teams, and select Sign up for free. Type in your Microsoft account email and select Next. Choose an option and select Next. Enter your password and select Sign in. Microsoft teams registered trademark. Microsoft Teams is the hub for team collaboration in Microsoft 365 that integrates the people, content, and tools your team needs to be more engaged and effective.

• Right click the Transactions query and choose Merge
• Select ChartOfAccounts for the bottom table
• For the top query (Transactions) select Account, hold down CTRL and select Dept
• For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
• Change the Join type to 'Left Anti (rows only in first)'
• Click OK

And when the data gets to Power Query:

• Right click the NewColumn column –> Rename –> COA
• Click the Expand icon on the top right of the COA column
• Leave all the defaults and click OK

You should now have this:

So this time, we only see the records from the left table (Transactions) that had no matching record in the ChartOfAccounts (right) table. How cool is that? This allows us to immediately identify records with no matches at all.

Let's finalize this query as well:

Just follow this link:https://discordapp.com/oauth2/authorize?&clientid=73482753&sc. Sidekick is a Discord assistant for war base calling, researching new recruits, competing with your clanmates for top looter, smart upgrade recommendations, and much more. Sidekick discord.

• Change the name of the query to Left Anti
• Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

By this point I'm sure you can predict where this is going. So let's get to it:

• Right click the Transactions query and choose Merge
• Select ChartOfAccounts for the bottom table
• For the top query (Transactions) select Account, hold down CTRL and select Dept
• For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
• Change the Join type to 'Right Anti (rows only in second)'
• Click OK

And when the data gets to Power Query:

• Right click the NewColumn column –> Rename –> COA
• Click the Expand icon on the top right of the COA column
• Leave all the defaults and click OK

And, in anti-climactic fashion (ha!) we end up with the following:

Yes indeed, as you probably predicted, only the records shown highlighted in yellow in the ChartOfAccounts (right) table show up in this set. No record that has a match in the Transactions (left) table, nor any records in the Transactions table without a match in the ChartOfAccounts table show up.

We can now finish our final comparison:

• Change the name of the query to Right Anti
• Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

I think this is pretty cool stuff. If you'd asked me how many ways there are to join data, I think I would have been hard pressed to answer six until I wrote this up. But seeing it laid out in full, with each option detailed… I can see where these would each be useful in their own right. I hope you enjoyed taking the journey with me here!

Oh… and by the way… if you'd like to download one workbook with all six join types included… you can do that here.

Syntax

Tutorial with example

In today's video I will show you how to do a full join using DAX.

Want to learn more about other types of join possible in Power BI?

In the video below, I go through all the different types of joins and how to do them in Power Query:

Download the excel demo file

Joins using DAX:

And the explanation with videos:

he generate function is used to join tables. With generate, you can do an inner join and outer join of tables using DAX:

GENERATE, GENERATEALL:

NATURALINNERJOIN, NATURALLEFTOUTERJOIN:

04:35 Innerjoin
07:59 Outer join

In this video I will show you how to do an anti join of tables using DAX:

EXCEPT:

Download example file:

Link to sample pbix here

Remarks

There is no sort order guarantee for the results.
Columns being joined on must have the same data type in both tables.
Only columns from the same source table (have the same lineage) are joined on. For example, Products[ProductID], WebSales[ProductdID], StoreSales[ProductdID] with many-to-one relationships between WebSales and StoreSales and the Products table based on the ProductID column, WebSales and StoreSales tables are joined on [ProductID]
Strict comparison semantics are used during join. There is no type coercion; for example, 1 does not equal 1.0.

• Joining tables in Power BI with Power Query and DAX

• EXCEPT
• GENERATEALL
• NATURALLEFTOUTERJOIN

Power Bi Video Tutorial