Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
=SUMPRODUCT((C2<=$D$2:$D$84000)*(D2>=$C$2:$C$84000)*(A2=$A$2:$A$84000))>1
I 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.
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 |
Thanks for your support in this.
Solved! Go to Solution.
Hi @rajrajsha ,
Hope this case can help you.
Hi @rajrajsha ,
Hope this case can help you.
Hi v-frfei-msft, Thanks for addressing my query. I will look into it and will let you know the status. Thanks you soo much for taking some time for suggesting the solutions. Im really happy to have such community and imso proud to have it.
Thanks
Rajsha
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.