Forum Discussion

JamieH's avatar
JamieH
Icon for Helper I rankHelper I
3 years ago

Left Join to table with multiple records but return one row

I need help with joining two tables in PowerQuery and hoping someone smart can assist.

 

I need to join a TableA which has one record per AppID to TableB which has multiple AppID's (the Primary Key), all with different SystemDate and SystemRecord fields. 

The join needs to bring back just one row and there are some rules around which row should be returned.

 

The logic should look to bring back the row where AppID match across the two tables, and

bring back the row from TableB with the most recent SystemDate if that row has a SystemRecord of either "Q" or "F".  If the row with most recent SystemDate has a SystemRecord of "T" then we should bring back the last row with a SystemRecord of "Q".

 

If TableB only has rows for that AppID with a SystemRecord of "T" then that should be returned. 

 

Does anyone have any suggestions?

 

Thanks in advance

 

Need Help

2 Replies

    • JamieH's avatar
      JamieH
      Icon for Helper I rankHelper I
      3 years ago

      Hi, 

       

      Here is some data examples.  The highlighted rows are the rows to bring back on the join, using the above written logic:

       

      TableA

      AppID
      1001
      2001
      3001
      4001

       

      TableB

      Row NumberAppIDSystemDateSystemRecordColumn4Column5
      1100105 February 2023Q  
      2100107 February 2023T  
      3100106 February 2023Q  
      4100108 February 2023T  
      5200108 February 2023F  
      6200106 February 2023Q  
      7200108 February 2023T  
      8300105 February 2023T  
      9400104 February 2023Q  
      10400104 February 2023T