My current data structure has a relationship where our Client Database which holds key information (name, dob etc) is the central node and various other tables of data relate to this via a "ClientRecord" field to allow for data to be related cross tables.
One of these related tables is our "Referral" information and is that particular entry point into our system, note that each client could have multiple referrals if they engage with us multiple times so its important that I collect this data against their referal, not their client record.
I have a second table that holds assessment results, this is related to the main client database with the same ClientRecord column. What I am wanting to achieve is find the first assessment for each referral which will lookup based upon the ClientRecord value and then the first record after the creation of that referral. I am then wanting to do the same with the Last record after the referral which should show the score for their First Assessment and then their Last Assessment since their referral.
Another example where I am wanting to do the same thing pretty much is with our recorded activities, I want to retrieve the first activity creation date after the individual referral creation date. The aim is to be able to calculate the amount of time between referral and first contact for each referral record.
I am not sure if I am explaining this properly but I have been trying to sort this for a while and made no progress at all. Any help would be appreciated.
Edit - My latest attempt which just loads forever and never does anything: = Table.AddColumn(#"Renamed Columns", "Custom", (w) => Table.SelectRows(#"EDE-Q 6 0 Form Responses", (x) => x[ClientRecord] = w[ClientRecord] and x[Created] >= w[Created]))
