Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Keith011
Helper III
Helper III

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

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 AddressStart TimeEnd Time
abc@gmail.com7:00:00 PM8:30:00 PM
abc@gmail.com7:01:00 PM8:35:00 PM
abc@gmail.com7:02:00 PM8:37:00 PM
abc@gmail.com7:03:00 PM8:31:00 PM
abc@gmail.com7:04:00 PM8:29:00 PM
abc@gmail.com7:05:00 PM8:13:00 PM

 

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

Email AddressStart TimeEnd Time
abc@gmail.com7:05:00 PM8: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!

2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

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"

View solution in original post

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}

View solution in original post

3 REPLIES 3
Keith011
Helper III
Helper III

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),

 

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
Super User
Super User

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"

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors