Power Query. Countifs between tables. 3 conditions

[Anonymous]

Hi,

I have 2 tables as below  - 

Table1 -

Employee ID	Date
1001	9/12/2019
1006	9/20/2019
1001	9/15/2019
1010	10/12/2019
1004	10/2/2019
1009	9/30/2019

Table2 - 

Employee ID	Check in Date	Check out Date
1001	8/29/2019	8/31/2019
1004	8/20/2019	8/22/2019
1001	9/12/2019	9/15/2019
1005	9/20/2019	9/22/2019
1008	9/15/2019	8/16/2019
1004	10/12/2019	10/13/2019

I need to count the rows in Table 2 which satisfy the following conditions - 

1. Table1[Employee ID] = Table2[Employee ID]

2. Table1[Date] >= Table2[Check in Date]

3. Table1[Date] <= Table2[Check out Date]

I added a column to Table1 for which the code looks like this-

= Table.AddColumn(#"Prev Step", "Flag", each (let tdate = [Date], id = [Employee ID] in Table.RowCount(Table.SelectRows(Table2,each [Check in Date] <= tdate and [Check out Date] >=tdate and [Employee ID] = id))))

The code seems to work well for small datasets, but my data has ~200,000 rows and the query seems to run forever. (Though the input file is 20MB, the query keeps loading data beyond 1GB and still does not finish).

Can you please advise?

[Anonymous · Accepted solution]

You will get a huge performance increase by Merging on Employee ID first (use inner join). Then expand Table2's columns. Now apply your date logic in a calculated column. You can then total how you see fit.

Regards,
Mike