Table.SelectRows with Condition check between two tables

[arunac]

Hi,

I have the two tables below

Transaction data

1) Person Number

2) Effective Date

3) Amount

Set up Data

1) Start Date

2) End Date

For each row in transaction record,  want to count the number of rows in Setup data that have end date >= Effective Date on transaction record. I tried the below combos on Transaction table record

1) Table.RowCount(Table.SelectRows(#"Set up Data",each #"Set up Data"[End Date]>= [Effective Date] . Error The field 'EFFECTIVE_DATE' of the record wasn't found.
Details:

1) Table.RowCount(Table.SelectRows(#"Set up Data",each #"Set up Data"[End Date]>= #"Transaction data"[Effective Date]

Error: Cyclic reference error 

Can you let me know what is the proper syntax to refer the current table for fetching criteria

[AlienSx]

Hi, @arunac first, create a list of end dates. Then add new column to transaction data table that calculations how many list items are >= than current effective date.

ed_list = List.Buffer(#"Set up Data"[End Date]),
count_column = Table.AddColumn(#"Transaction data", "count", each List.Count(List.Select(ed_list, (x) => x >= [Effective Date])))