Excel Forum

SumProduct with PowerQuery

Rajsha B

India

Member

Members

Forum Posts: 22

Member Since:
April 7, 2020

Offline

April 7, 2020 - 10:36 pm

Hi All, I am new user to this forum. First of all thanks for launching such forum to enhance excel based knowledge.

I have a scenario with Power Query where i need to run the query with sumproduct. The formula which i used in excel is

=SUMPRODUCT((C2<=$D$2:$D$84000)*(D2>=$C$2:$C$84000)*(A2=$A$2:$A$84000))>1

This i want to implement with power query for the below data in column "E" (In "Formula" Header) . The sample data is below.

Rep	Code	Date/Time In	Date/Time Out	Formula
11	21381	4/18/18 2:44 AM	4/18/18 6:49 AM	FALSE
11	21381	4/18/18 7:16 AM	4/18/18 7:16 AM	FALSE
11	21381	4/26/18 12:24 PM	4/26/18 3:24 PM	FALSE
13	70105	4/20/17 4:52 AM	4/20/17 5:59 AM	FALSE
13	97813	8/30/19 3:11 PM	8/30/19 3:29 PM	FALSE
13	43100	12/18/19 5:22 PM	12/18/19 8:25 PM	FALSE
14	52334	4/28/17 6:03 PM	4/28/17 7:26 PM	FALSE
14	6018	5/10/17 6:28 PM	5/11/17 12:30 AM	FALSE
14	81033	5/16/17 3:09 PM	5/16/17 7:26 PM	FALSE
14	11259	5/16/17 10:39 PM	5/17/17 12:50 AM	FALSE
14	10987	5/17/17 12:51 AM	5/17/17 1:27 AM	FALSE
14	10951	5/17/17 1:28 AM	5/17/17 2:45 AM	FALSE
14	1970	5/22/17 12:47 PM	5/22/17 2:05 PM	FALSE
14	1970	5/22/17 2:20 PM	5/22/17 9:18 PM	FALSE
14	71351	5/24/17 7:37 PM	5/24/17 9:04 PM	FALSE
14	19254	5/26/17 1:29 AM	5/26/17 2:30 AM	FALSE
14	65914	6/6/17 6:22 PM	6/6/17 8:45 PM	FALSE
14	71351	6/6/17 8:46 PM	6/6/17 10:11 PM	FALSE
14	10431	6/13/17 11:27 AM	6/13/17 1:17 PM	TRUE
14	10951	6/13/17 11:27 AM	6/13/17 1:32 PM	TRUE
14	71470	6/13/17 1:18 PM	6/13/17 1:50 PM	TRUE
14	73693	6/17/17 3:03 PM	6/17/17 6:35 PM	FALSE
14	71470	6/27/17 11:06 PM	6/28/17 1:05 AM	FALSE
14	92797	6/28/17 1:07 AM	6/28/17 2:05 AM	FALSE
14	77637	7/11/17 6:10 PM	7/11/17 8:01 PM	TRUE
14	46236	7/11/17 6:10 PM	7/11/17 9:30 PM	TRUE
14	77637	7/24/17 7:56 PM	7/24/17 10:59 PM	FALSE
14	31439	7/25/17 10:53 AM	7/25/17 1:21 PM	FALSE
14	34574	7/31/17 1:42 PM	7/31/17 5:13 PM	FALSE
14	21452	8/7/17 9:43 AM	8/7/17 12:46 PM	FALSE
14	21423	8/9/17 8:56 PM	8/9/17 9:25 PM	FALSE
14	95377	8/11/17 7:14 PM	8/11/17 9:35 PM	FALSE
14	21423	8/21/17 3:56 PM	8/21/17 5:22 PM	FALSE
14	1202	9/18/17 9:22 AM	9/18/17 11:06 AM	FALSE
14	32670	9/18/17 11:07 AM	9/18/17 11:21 AM	FALSE
14	5363	10/2/17 7:31 AM	10/2/17 9:34 AM	FALSE
14	32670	10/7/17 4:37 AM	10/7/17 6:25 AM	FALSE
14	81711	10/11/17 1:24 PM	10/11/17 2:25 PM	FALSE
14	81711	10/17/17 5:27 PM	10/17/17 9:35 PM	FALSE
14	17040	11/2/17 9:55 AM	11/2/17 11:29 AM	FALSE
14	84700	11/6/17 8:15 AM	11/6/17 12:16 PM	FALSE
14	54158	11/20/17 3:00 PM	11/20/17 4:49 PM	FALSE
14	1323	11/27/17 8:29 AM	11/27/17 12:29 PM	FALSE

Thanks for the support you team provide. Thanks a lot.

See Answer

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 880

Member Since:
December 7, 2016

Offline

April 8, 2020 - 7:37 am

Hello,

It seems to me that you could use IF statements by adding a conditional column.
https://www.myonlinetraininghu.....statements

Br,
Anders

Rajsha B

India

Member

Members

Forum Posts: 22

Member Since:
April 7, 2020

Offline

April 8, 2020 - 2:54 pm

Hi Anders,

Thanks for your quick reply. I tried with the above information provided in the link. But it is not working as i need. Can you please advise me the best way approach.

Thanks

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 880

Member Since:
December 7, 2016

Offline

April 8, 2020 - 7:50 pm

Hello,

If you can upload a sample file and describe in text what the logics are. What is it you want as end result?

Rajsha B

India

Member

Members

Forum Posts: 22

Member Since:
April 7, 2020

Offline

April 8, 2020 - 10:22 pm

Hi Sir,

I have uploaded sample date and also, i have mentioned the description/ what result that i need in the sample excel file itself.

Thanks for you help

Rajsha

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 880

Member Since:
December 7, 2016

Offline

April 9, 2020 - 2:18 am

Hello,

I am afraid I don't understand what you are looking for here. You write "Need to find the overlapping dates", what do you mean by overlapping? All the dates/times in column C are less than those in column D, except for row 3 where they are equal.

There are some dates/times in column C with same Rep# that has same date/time value, but they are not the same in column D. What is it that defines when overlapping is true or false?

Br,
Anders

Rajsha B

India

Member

Members

Forum Posts: 22

Member Since:
April 7, 2020

Offline

April 10, 2020 - 3:20 pm

Hi Anders,

Sorry for the late reply below is what i need to do in the sheet.

I have multiple columns with 2 dates columns. I need to count the rows that meets the 2 dates criteria.

objective: Identify obvious potential overlaps between the dates based on Order#. If one order# is started before the last one is completed, we should flag that as Yes. This overlapping should be done based on Rep#.

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 excel ln the previous chain of the mail above. For better understanding i changed my sumproduct formula to countifs..

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

Thanks.

Anders Sehlstedt

Eskilstuna, Sweden

VIP

Members

Trusted Members

Forum Posts: 880

Member Since:
December 7, 2016

Offline

April 11, 2020 - 6:14 pm

Hello,

I have looked up on this topic and it seems it is a lot more difficult to get what you want using Power Query. Although it seems doable but it also seems you need to master the M formula language, which I don't.

Hopefully you will find a solution.

Br,
Anders

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1844

Member Since:
November 8, 2013

Offline

April 11, 2020 - 9:45 pm

Hi Rajsha,

Add your table to power query, then add a new column with this formula:

= Table.AddColumn(#"Changed Type", "Count", (x)=> Table.RowCount(Table.SelectRows(#"Changed Type", each (_[#"Rep#"] = x[#"Rep#"]) and (_[#"Date/Time In"] >= x[#"Date/Time In"])and (_[#"Date/Time Out"] <= x[#"Date/Time Out"]) )))

Basically, you can get the most part of the syntax by filtering the table, after filtering you will get a step like this (make sure you filter DateTime In column with a date filter, After):

= Table.SelectRows(#"Filtered Rows", each [#"Date/Time In"] >= #datetime(2018, 4, 18, 2, 44, 0) and [#"Date/Time In"] <= #datetime(2018, 4, 26, 12, 24, 0))

Simply edit the formula in the formula bar, change the second time In to Time Out, and add the Rep criteria:

= Table.SelectRows(#"Filtered Rows", each [#"Rep#"]=2 and [#"Date/Time In"] >= #datetime(2018, 4, 18, 2, 44, 0) and [#"Date/Time Out"] <= #datetime(2018, 4, 26, 12, 24, 0))

The red parts you can edit in the formula bar, no need to enter into Advanced editor. After you make these edits, copy the final text of this edited formula, we will use it in a new column.

You can delete the steps you did to get the syntax right, and now you can add a new column with the formula you have created, Paste the formula you saved before, and click ok to finalize the process. Don't worry if it will return errors, we still have to make some adjustments in the formula bar, we have almost all we need in the formula.

each must be replaced with: (x)=>

[#"Rep#"] must be replaced with _[#"Rep#"] (just put an underscore before what is in there already)

[#"Date/Time In"] must be replaced with _[#"Date/Time In"] (just put an underscore before what is in there already)

[#"Date/Time Out"] must be replaced with _[#"Date/Time Out"] (just put an underscore before what is in there already)

2 must be replaced with x[#"Rep#"]

#datetime(2018, 4, 18, 2, 44, 0) must be replaced with x[#"Date/Time In"]

#datetime(2018, 4, 26, 12, 24, 0)) must be replaced with x[#"Date/Time Out"]

The new column will contain tables, each table will contain the rows matching the current row criterias, all you have to do is to wrap the formula into Table.CountRows, to count them.

Or, just add a new column with the formula provided at the beginning of this message, I tried to explain how you can do it yourself using the menu and formula bar only.

Answers Post

Rajsha B

India

Member

Members

Forum Posts: 22

Member Since:
April 7, 2020

Offline

April 13, 2020 - 5:18 pm

Hi Catalin Bombea,

Thank you soo much.. This is what I needed, I'm just trying to understand the code what you did, but this is really fantastic. Hats off to you sir. Sorry for the trouble i gave. The explanation that you gave was very helpful. Kudos to you.. Smile Smile