Get the earliest start time and latest end time base on a specific time

[Keith011]

I need help to find the earliest time / latest time with condition

This table (Table name = Table1) below is an example data that I have

Email Address	Start Time	End Time
abc@gmail.com	7:00:00 PM	8:30:00 PM
abc@gmail.com	7:01:00 PM	8:35:00 PM
abc@gmail.com	7:02:00 PM	8:37:00 PM
abc@gmail.com	7:03:00 PM	8:31:00 PM
abc@gmail.com	7:04:00 PM	8:29:00 PM
abc@gmail.com	7:05:00 PM	8:13:00 PM

below here (table name = Table2) is the output that i would expect

Email Address	Start Time	End Time
abc@gmail.com	7:05:00 PM	8:30:00 PM

What I'm currently doing is using this in Table2, but this gives me the earliest/latest time of that record (with no conditions)

Column = CALCULATE(

MIN(Table1[startTime]),

FILTER(Table1,

'Table2'[Email Address]=Table1[Email Address]))

I want to get the earliest start time from Table1 that is after or Equals 7:05:00 PM and the latest End Time Before or Equals 8:30:00 PM

the condition differs whether start time is after or Equals 7:05:00 PM or End Time is Before or Equals 8:30:00. So I'm thinking to have a look up table (or some sort) for the condition so that all I need to do is update the Lookup table of the start time and end time and the formula can return whatever time that I need

Thanks in advance!

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

I have put 7:05PM and 8:30PM in code but you can put them in parameters (preferred) or table/list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKdkjPTczM0UvOz1XSUTK3MjAAIoUAXyDHwsoYxonVwarWEFmtKX61RshqzfGrNUZWa4hfrQmSWiNL/GpNkdQaGiOpraiswhcOBvjVGuIMByzm4gwHLGpxhgMWtTjDAYtaC6zhEAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Email Address" = _t, #"Start Time" = _t, #"End Time" = _t]),
    StartTime = #time(19,5,0),
    EndTime = #time(20,30,0),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email Address", type text}, {"Start Time", type time}, {"End Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Start Time", each if [Start Time] >= StartTime then [Start Time] else null, type time),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "New End Time", each if [End Time]<=EndTime then [End Time] else null, type time),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Email Address"}, {{"Start Time", each List.Min([New Start Time]), type time}, {"End Time", each List.Max([New End Time]), type time}})
in
    #"Grouped Rows"

[Keith011]

It's working! @Vijay_A_Verma 

1 more thing

Suppose the start time and end time is dynamic (in a way where i can update the value and the M code reads from it) and I want it to be in a separate table by itself. How would the M code change? for this part :

    StartTime = #time(19,5,0),
    EndTime = #time(20,30,0),

[Vijay_A_Verma · Accepted solution]

Suppose you are putting this in a table named TableA. Column name is Time and 1st row has start time and second row has end time.

Then #time(19,5,0) will be replaced with TableA[Time]{0} and #time(20,30,0) will be replaced with TableA[Time]{1}

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

I have put 7:05PM and 8:30PM in code but you can put them in parameters (preferred) or table/list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKdkjPTczM0UvOz1XSUTK3MjAAIoUAXyDHwsoYxonVwarWEFmtKX61RshqzfGrNUZWa4hfrQmSWiNL/GpNkdQaGiOpraiswhcOBvjVGuIMByzm4gwHLGpxhgMWtTjDAYtaC6zhEAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Email Address" = _t, #"Start Time" = _t, #"End Time" = _t]),
    StartTime = #time(19,5,0),
    EndTime = #time(20,30,0),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email Address", type text}, {"Start Time", type time}, {"End Time", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "New Start Time", each if [Start Time] >= StartTime then [Start Time] else null, type time),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "New End Time", each if [End Time]<=EndTime then [End Time] else null, type time),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Email Address"}, {{"Start Time", each List.Min([New Start Time]), type time}, {"End Time", each List.Max([New End Time]), type time}})
in
    #"Grouped Rows"