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

[Applicable88]

Hello,

I have machine data here. Every action of that machine is creating a new row of data. And I want to get a column with total time between the very first action and the very last action of a specific mission, which is not on the same row. Only when a mission has only one row, than its possible. 

This is my sample Table in PowerQuery. The TotalTime column is the wanted column:

MissionNumber	ActionName	Date	Starttime	Finishtime	TotalTime
1	Start of Action	28.07.2021	28.07.2021 08:00:00	28.07.2021 08:01:00	00:10:00
1	Moving	28.07.2021	28.07.2021 08:01:00	28.07.2021 08:05:00	00:10:00
1	Delivering	28.07.2021	28.07.2021 08:05:00	28.07.2021 08:09:00	00:10:00
1	Finish of Action	28.07.2021	28.07.2021 08:09:00	28.07.2021 08:10:00	00:10:00
2	Start of Action	28.07.2021	28.07.2021 09:00:00	28.07.2021 09:02:00	00:18:00
2	Moving	28.07.2021	28.07.2021 09:02:00	28.07.2021 09:09:00	00:18:00
2	Delivering	28.07.2021	28.07.2021 09:09:00	28.07.2021 09:15:00	00:18:00
2	Finish of Action	28.07.2021	28.07.2021 09:15:00	28.07.2021 09:18:00	00:18:00

Thank you very much in advance. 
If the calculated column is not availabe in HH:mm:ss, then seconds would be also enough.

Best regards.

[Payeras_BI · Accepted solution]

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"

[MrSujay · Accepted solution]

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.

[Payeras_BI]

Hi @Applicable88,

I hope this helps.

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 Rows" = Table.Group(#"Changed Type", {"MissionNumber"}, {{"Total Time", each List.Max([Finishtime]) - List.Min([Starttime]), Duration.Type}, {"All", each _, type table [MissionNumber=nullable number, ActionName=nullable text, Date=nullable number, Starttime=nullable datetime, Finishtime=nullable datetime]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"ActionName", "Date", "Starttime", "Finishtime"}, {"ActionName", "Date", "Starttime", "Finishtime"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All",{"MissionNumber", "ActionName", "Date", "Starttime", "Finishtime", "Total Time"})
in
    #"Reordered Columns"

[Applicable88]

Hello @Payeras_BI , thank you very much for the effort. Did you get there through clicking within the menu or wrote the entire part in the advanced editor?

Best. 

[Payeras_BI · Accepted solution]

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"

[MrSujay]

explain more details about your current result and table with your expected output.

[Applicable88]

@MrSujay the automatic formatting of the table makes it really unreadable. Here I put the sample table with enough spaces:

MissionNumber	ActionName	Date		Starttime		Finishtime		TotalTime
1	Start of Action	28.07.2021		28.07.2021 08:00:00		28.07.2021 08:01:00		00:10:00
1	Moving	28.07.2021		28.07.2021 08:01:00		28.07.2021 08:05:00		00:10:00
1	Delivering	28.07.2021		28.07.2021 08:05:00		28.07.2021 08:09:00		00:10:00
1	Finish of Action	28.07.2021		28.07.2021 08:09:00		28.07.2021 08:10:00		00:10:00
2	Start of Action	28.07.2021		28.07.2021 09:00:00		28.07.2021 09:02:00		00:18:00
2	Moving	28.07.2021		28.07.2021 09:02:00		28.07.2021 09:09:00		00:18:00
2	Delivering	28.07.2021		28.07.2021 09:09:00		28.07.2021 09:15:00		00:18:00
2	Finish of Action	28.07.2021		28.07.2021 09:15:00		28.07.2021 09:18:00		00:18:00

As I stated before my expected outcome is the "TotalTime" column on the right side. This is the duration of the very first action until the very last action of one specific  mission number. For Example MissionNumber 1 started at 8o'clock and the finish time of that complete mission is not on the same row. It is at 08:10:00. So the total time of that mission is 10 minutes .

Hope I was clear.

Best.