How to conditionally expand a merged query

[michaelu1]

I have 2 tables:
One has a field of Resident IDs and next to each tenant it shows who the prior Resident ID was.

The other table has a list of charges for each Resident ID.

I'm trying to join on the overall Unit ID and then expand the new column twice: once where the resident ID equals the current ID and then again where the resident ID equals the prior ID, like below:

How do I add a column where it conditionally expands the joins table?

 On the attached file, Leases_2 shows the expected result. I got there by joining twice to the charges table on the resident IDs. I assume that it is a less efficient method as my charges table contains over 1M rows and has a number of transformations, so I assume its more efficent to join it once and then expand twice instead.

Here is the file: https://drive.google.com/file/d/1N5B0eunp2ZyfqOD0a2OkXybADhM9Vr4I/view?usp=drive_link

[amitchandak]

@michaelu1 , In this case, you have to join twice.

Or You can try the function Power BI- Power Query Table.AddJoinColumn: https://youtu.be/-mThmfSaiV8

[michaelu1]

@amitchandak 
Why can't I conditionally expand? I've done it before using M code like this: 

= Table.AddColumn(#"Expanded GroupAllDate","GroupDelqDate", each Table.Max(Table.SelectRows([ALL], each [Delq Type] = "Delq" or [Delq Type] = "ATCF"), "LastDateTime"))

The only thing I can't figure out is how to reference the original query to compare the 2 columns.

Assuming I use Table.AddJoinColumn would that need to cause the joinded table to load twice?