Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Table joins leaving data out

I have 2 tables with years, months, location and area number, amongst other data.  I am doing a "merge queries" and having Table A on the left and Table B on the right.  I've tried doing left joins, right, and full outer - the issue is that that Table B has some area numbers that are not in Table A. When I do the join, I want all area numbers from both tables per location. In my case, when I join, the final table does not include the other area numbers from Table B. 

Below is an example of what my data would be like:

 

Table A

YearMonthLocationArea
202201LA41
202201LA52
202202DC65
202202DC78

 

Table B

YearMonthLocationArea
202201LA41
202201LA52
202201LA88
202202DC65
202202DC78

Comparing these 2 example tables, LA in table B has area 88. After my join, the final table only lists LA-41 and LA-52, I can't get it to bring in LA-88. 

 

 

#"Merged Queries" = Table.NestedJoin(#"Previous Transformation", 
                   {"Year", "Month", "Location", "Area"}, TableA, 
                   {"Year", "Month", "Location", "Area"}, "TableB", 
                   JoinKind.LeftOuter)

 

Which join should I do instead or what am I doing wrong?

  • Hi Anonymous ,

     

    Please put table B on top and table A below.

     

    Then expand table. Is this the result you want?

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

2 Replies