Sumproduct with Power Query

Question

Hi All,In excel it is very easy to write sumproduct, but how to do the same formula with Power Query.Belwo is my formula which goes in excel is&nbsp;=SUMPRODUCT((C2&lt;=$D$2:$D$84000)*(D2&gt;=$C$2:$C$84000)*(A2=$A$2:$A$84000))&gt;1I need to do the same formula with Power Query. Can some one help me in this case to write the code in PQ. For bette understanding i linked my data image and sample data below.&nbsp;&nbsp;&nbsp;&nbsp;RepNumberTime InTime OutStatus1380393/10/17 8:47 AM3/10/17 9:10 AMFALSE1380253/10/17 1:34 PM3/10/17 2:14 PMFALSE1381263/22/17 6:00 AM3/22/17 7:14 AMFALSE1382174/14/17 6:33 AM4/14/17 7:33 AMFALSE1382174/20/17 4:52 AM4/20/17 5:59 AMFALSE1312418/30/19 3:11 PM8/30/19 3:29 PMFALSE13128012/18/19 5:22 PM12/18/19 8:25 PMFALSE1482554/28/17 6:03 PM4/28/17 7:26 PMFALSE1483105/10/17 6:28 PM5/11/17 12:30 AMFALSE1483385/16/17 3:09 PM5/16/17 7:26 PMFALSE1483415/16/17 10:39 PM5/17/17 12:50 AMFALSE1483415/17/17 12:51 AM5/17/17 1:27 AMFALSE1483415/17/17 1:28 AM5/17/17 2:45 AMFALSE1483605/22/17 12:47 PM5/22/17 2:05 PMFALSE1483605/22/17 2:20 PM5/22/17 9:18 PMFALSE1483675/24/17 7:37 PM5/24/17 9:04 PMFALSE1483715/26/17 1:29 AM5/26/17 2:30 AMFALSE1484166/6/17 6:22 PM6/6/17 8:45 PMFALSE1483676/6/17 8:46 PM6/6/17 10:11 PMFALSE1484416/13/17 11:27 AM6/13/17 1:17 PMTRUE1483416/13/17 11:27 AM6/13/17 1:32 PMTRUE1484476/13/17 1:18 PM6/13/17 1:50 PMTRUE1484776/17/17 3:03 PM6/17/17 6:35 PMFALSE1484476/27/17 11:06 PM6/28/17 1:05 AMFALSE1485096/28/17 1:07 AM6/28/17 2:05 AMFALSE1485577/11/17 6:10 PM7/11/17 8:01 PMTRUE1485547/11/17 6:10 PM7/11/17 9:30 PMTRUE1485577/24/17 7:56 PM7/24/17 10:59 PMFALSE1486137/25/17 10:53 AM7/25/17 1:21 PMFALSE1486337/31/17 1:42 PM7/31/17 5:13 PMFALSE1486628/7/17 9:43 AM8/7/17 12:46 PMFALSE1486628/9/17 8:56 PM8/9/17 9:25 PMFALSE1486798/11/17 7:14 PM8/11/17 9:35 PMFALSE1486628/21/17 3:56 PM8/21/17 5:22 PMFALSE1488209/18/17 9:22 AM9/18/17 11:06 AMFALSE1488139/18/17 11:07 AM9/18/17 11:21 AMFALSE14887010/2/17 7:31 AM10/2/17 9:34 AMFALSE&nbsp;Thanks for your support in this.&nbsp;

v-frfei-msft · Accepted Answer

Hi&nbsp;rajrajsha&nbsp;,
&nbsp;
Hope this case can help you.
https://www.myonlinetraininghub.com/excel-forum/power-query/countifs-or-sumproduct-in-power-query-or-dax
&nbsp;

Forum Discussion

Sumproduct with Power Query

2 Replies

Related content

Power Query dates

Data transform using power query

ayuda en power query

Power BI downloaded PBIX does not contain Power Query queries

Power Query

Rep	Number	Time In	Time Out	Status
13	8039	3/10/17 8:47 AM	3/10/17 9:10 AM	FALSE
13	8025	3/10/17 1:34 PM	3/10/17 2:14 PM	FALSE
13	8126	3/22/17 6:00 AM	3/22/17 7:14 AM	FALSE
13	8217	4/14/17 6:33 AM	4/14/17 7:33 AM	FALSE
13	8217	4/20/17 4:52 AM	4/20/17 5:59 AM	FALSE
13	1241	8/30/19 3:11 PM	8/30/19 3:29 PM	FALSE
13	1280	12/18/19 5:22 PM	12/18/19 8:25 PM	FALSE
14	8255	4/28/17 6:03 PM	4/28/17 7:26 PM	FALSE
14	8310	5/10/17 6:28 PM	5/11/17 12:30 AM	FALSE
14	8338	5/16/17 3:09 PM	5/16/17 7:26 PM	FALSE
14	8341	5/16/17 10:39 PM	5/17/17 12:50 AM	FALSE
14	8341	5/17/17 12:51 AM	5/17/17 1:27 AM	FALSE
14	8341	5/17/17 1:28 AM	5/17/17 2:45 AM	FALSE
14	8360	5/22/17 12:47 PM	5/22/17 2:05 PM	FALSE
14	8360	5/22/17 2:20 PM	5/22/17 9:18 PM	FALSE
14	8367	5/24/17 7:37 PM	5/24/17 9:04 PM	FALSE
14	8371	5/26/17 1:29 AM	5/26/17 2:30 AM	FALSE
14	8416	6/6/17 6:22 PM	6/6/17 8:45 PM	FALSE
14	8367	6/6/17 8:46 PM	6/6/17 10:11 PM	FALSE
14	8441	6/13/17 11:27 AM	6/13/17 1:17 PM	TRUE
14	8341	6/13/17 11:27 AM	6/13/17 1:32 PM	TRUE
14	8447	6/13/17 1:18 PM	6/13/17 1:50 PM	TRUE
14	8477	6/17/17 3:03 PM	6/17/17 6:35 PM	FALSE
14	8447	6/27/17 11:06 PM	6/28/17 1:05 AM	FALSE
14	8509	6/28/17 1:07 AM	6/28/17 2:05 AM	FALSE
14	8557	7/11/17 6:10 PM	7/11/17 8:01 PM	TRUE
14	8554	7/11/17 6:10 PM	7/11/17 9:30 PM	TRUE
14	8557	7/24/17 7:56 PM	7/24/17 10:59 PM	FALSE
14	8613	7/25/17 10:53 AM	7/25/17 1:21 PM	FALSE
14	8633	7/31/17 1:42 PM	7/31/17 5:13 PM	FALSE
14	8662	8/7/17 9:43 AM	8/7/17 12:46 PM	FALSE
14	8662	8/9/17 8:56 PM	8/9/17 9:25 PM	FALSE
14	8679	8/11/17 7:14 PM	8/11/17 9:35 PM	FALSE
14	8662	8/21/17 3:56 PM	8/21/17 5:22 PM	FALSE
14	8820	9/18/17 9:22 AM	9/18/17 11:06 AM	FALSE
14	8813	9/18/17 11:07 AM	9/18/17 11:21 AM	FALSE
14	8870	10/2/17 7:31 AM	10/2/17 9:34 AM	FALSE