Forum Discussion

arunac's avatar
arunac
New Member
2 years ago

Table.SelectRows with Condition check between two tables

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

Need Help
Show and Tell

1 Reply

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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])))