Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
vanessafvg
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.