Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Power Query. Countifs between tables. 3 conditions

Hi,

 

I have 2 tables as below  - 

 

Table1 -

Employee IDDate
10019/12/2019
10069/20/2019
10019/15/2019
101010/12/2019
100410/2/2019
10099/30/2019

 

Table2 - 

Employee IDCheck in DateCheck out Date
10018/29/20198/31/2019
10048/20/20198/22/2019
10019/12/20199/15/2019
10059/20/20199/22/2019
10089/15/20198/16/2019
100410/12/201910/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?

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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