cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Osmento
New Member

Merging table with a left outer join

Hi Everyone,

 

I have two table that I am trying to merge data in.

 

In my Travel Data table I have an Origin Code, which relates to the airport of departure.

 

In my Origin IATA Codes this is the Airport of departure code.

 

Merging these two tables with a left outer join, should give all the data from my travel data table with the updated information added from the Origin IATA codes Table.

Merge.PNG

 

 

In my travel data table every row has a Origin IATA Code (not unique) and all are valid Origin IATA Codes (unique), so it should be a straight match.

 

My data has 375109 rows and it says it matches 375109 rows.

 

When I update my changes

 

My total goes from 

Summary Before.PNG

 

To 

 

Merged Summary.PNG

I don't understand why there is a difference.

 

I have compared the before and after using Excel and I can locate where a row has been duplicated as in the example below, but I can't understand why it has acted this way.

 

Excel Workbooks.PNG

 

I am thinking that perhaps the join is wrong or the relationship?

 

TIA

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Merging table with a left outer join

What is the result if you choose "Right Outer Join" instead of "Left Outer Join"?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Super User I
Super User I

Re: Merging table with a left outer join

@Osmento your excel tables, does it show the code you are joining on?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Osmento
New Member

Re: Merging table with a left outer join

So the origin is from the first table and everything from AQ onwards is from the left joined merge table.  So the merge has completed but it has also duplicated the data.

 

Excel Join.PNG

Super User III
Super User III

Re: Merging table with a left outer join

What is the result if you choose "Right Outer Join" instead of "Left Outer Join"?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Osmento
New Member

Re: Merging table with a left outer join

Yesterday I tried every type of join and didn't get the correct figure. 

 

When I tried the right outer today the fugure was correct.  Thanks for your help.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors