Hello,

It seems to me that you could use IF statements by adding a conditional column.
https://www.myonlinetraininghub.com/power-query-if-statements

Br,
Anders

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 

Hello,

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

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

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

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.

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

Hi Rajsha,

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

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.

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..

Thanks a lot

Rajsha.