Getting the total time between start and- finishtime on different rows

Hi Applicable88,

By Grouping using the UI and then modifying the resulting code to extract the duration in a single step.

In the version below everything is done through the UI:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqUchPU3BMLsnMzwOKGFnoGZjrGRkYGaJwFAwsrAwMgAhT1BAkGqsDMdA3vywzL52AOYZYzTFFNsclNSezLLWIsFmmWM2yRDbLLTMvsziDaF9aYjPR0ABmohFpwWaJNdiAokbIBhIONpgODFFLZHOICzZLrJ60tDI0RTaLlGCD6cUQtQCbGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MissionNumber = _t, ActionName = _t, Date = _t, Starttime = _t, Finishtime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MissionNumber", Int64.Type}, {"ActionName", type text}, {"Date", Date.Type}, {"Starttime", type datetime}, {"Finishtime", type datetime}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"MissionNumber"}, {{"All", each _, type table [MissionNumber=nullable number, ActionName=nullable text, Date=nullable date, Starttime=nullable datetime, Finishtime=nullable datetime]}, {"Starttime min", each List.Min([Starttime]), type nullable datetime}, {"Finishtime max", each List.Max([Finishtime]), type nullable datetime}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Total Time", each [Finishtime max]-[Starttime min]),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"ActionName", "Date", "Starttime", "Finishtime"}, {"ActionName", "Date", "Starttime", "Finishtime"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All",{"Starttime min", "Finishtime max"})
in
    #"Removed Columns"

Your date columns are not in a standard format. so first make it in one format .

In Query Editior replace '.' dot with "/". and and change column datatype to Date Time '3/14/2001 13:30:55 (m/d/yyyy hh:nn:ss)' .

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQouSSwqUchPU3BMLsnMzwOKGFnoGZjrGRkYGaJwFAwsrAwMgAhT1BAkGqsDMdA3vywzL52AOYZYzTFFNsclNSezLLWIsFmmWM2yRDbLLTMvsziDaF9aYjPR0ABmohFpwWaJNdiAokbIBhIONpgODFFLZHOICzZLrJ60tDI0RTaLlGCD6cUQtQCbGAsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MissionNumber = _t, ActionName = _t, Date = _t, Starttime = _t, Finishtime = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,".","/",Replacer.ReplaceText,{"Starttime"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".","/",Replacer.ReplaceText,{"Finishtime"})
in
    #"Replaced Value1"

Create new 3 Custom Columns :

1. First_Start

First_Start = 
VAR current_MissionNumber = (Query1[MissionNumber])
RETURN
CALCULATE(
    MIN(Query1[Starttime]),
    FILTER(
        ALL(Query1),
        Query1[MissionNumber] = current_MissionNumber
    )
)

and change column datatype to Date Time '3/14/2001 13:30:55 (m/d/yyyy hh:nn:ss)'

2. Last_finish

Last_Finish = 
VAR current_MissionNumber = (Query1[MissionNumber])
RETURN
CALCULATE(
    Max(Query1[Finishtime]),
    FILTER(
        ALL(Query1),
        Query1[MissionNumber] = current_MissionNumber
    )
)

and change column datatype to Date Time '3/14/2001 13:30:55 (m/d/yyyy hh:nn:ss)'

 3. TotalTime

TotalTime = 
VAR _Difference = Query1[Last_Finish] - Query1[First_Start] 
VAR _Days = INT(_Difference)
VAR _Hours = HOUR(_Difference)
VAR _Minutes = MINUTE(_Difference)
VAR _Seconds = SECOND(_Difference)
VAR _DaysToHours = _Days * 24
VAR _TotalHours = _DaysToHours + _Hours
RETURN
    FORMAT(_TotalHours, "00") & ":" & FORMAT(_Minutes, "00") & ":" & FORMAT(_Seconds,"00")

Now u can see Total Time column for each row.

Thanks,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.