Countif yes in Power Query

Question

How can I do Countifs of dates in Power query?

I have multiple columns with 2 dates columns. I need to count the rows that meet the 2 dates criteria of status.

Criteria and objective: Identify obvious potential overlaps. If one order# is started before the last one is completed, we should flag that as Yes.

My data model has a date table. My challenge is getting the current row in Rep# as criteria. Please see the sample data for more reference. The formula that I use is listed below. Also, I have attached data image.

=IF((COUNTIFS($C$2:$C$40,"<="&D2,$D$2:$D$40,">="&C2,$A$2:$A$40,"="&A2))>1,"YES","NO")

My challenge is getting the current row in Rep# as criteria. Please see the sample data for more reference.

  A    B         C                    D               E
15   84165  6/6/17 6:22 PM      6/6/17 8:45 PM        NO    
15   83671  6/6/17 8:46 PM      6/6/17 10:11 PM       NO    
15   84410  6/13/17 11:27 AM    6/13/17 1:17 PM      YES    
15   83410  6/13/17 11:27 AM    6/13/17 1:32 PM      YES    
15   84471  6/13/17 1:18 PM     6/13/17 1:50 PM      YES    
15   84773  6/17/17 3:03 PM     6/17/17 6:35 PM       NO    
15   84471  6/27/17 11:06 PM    6/28/17 1:05 AM       NO    
15   85092  6/28/17 1:07 AM     6/28/17 2:05 AM       NO    
15   85577  7/11/17 6:10 PM     7/11/17 8:01 PM      YES    
15   85546  7/11/17 6:10 PM     7/11/17 9:30 PM      YES

Thanks in Advance.

horseyride horseyride · Accepted Answer · 2020-04-10T13:14:45

Assuming data is in range Table1 with column headers as shown in your example then this should work

In the opened Power Query Editor window and on the ribbon bar, choose: Home => Advanced Editor and paste it in

The magic is in the 3rd line which grabs all matching rows based on criteria for current values of Rep, Time In and Time Out

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Rep#", Int64.Type}, {"Order#", Int64.Type}, {"Date/Time In", type datetime}, {"Date/Time Out", type datetime}}),
Total = Table.AddColumn(#"Changed Type", "Count",(i) => Table.RowCount(Table.SelectRows(#"Changed Type", each ([#"Rep#"] = i[#"Rep#"] and [#"Date/Time In"] <= i[#"Date/Time Out"] and [#"Date/Time Out"] >= i[#"Date/Time In"] ))), type number ),
#"Added Custom" = Table.AddColumn(Total, "YES_NO", each if [Count]>1 then "YES" else "NO") 
in #"Added Custom"

Countif yes in Power Query

1 Answers