cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors