Left Join to table with multiple records but return one row

[JamieH]

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

[serpiva64]

Hi,

please post sample data

[JamieH]

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 Number	AppID	SystemDate	SystemRecord	Column4	Column5
1	1001	05 February 2023	Q
2	1001	07 February 2023	T
3	1001	06 February 2023	Q
4	1001	08 February 2023	T
5	2001	08 February 2023	F
6	2001	06 February 2023	Q
7	2001	08 February 2023	T
8	3001	05 February 2023	T
9	4001	04 February 2023	Q
10	4001	04 February 2023	T