Compute duration when start time and end time are in the same column

[sciomac]

Hi everyone,

I hope someone can help me. I am trying to see the duration of some jobs (orders) but the table where everything is registered has Start_time and End_time in the same column. There are not two columns one for Start_time and a second one for End_time. You can see the data the way I have it.

And I would like to have the data in the Query Editor like this:

I am clueless, I have tried different approaches but nothing worked. I can provide sample data if anyone needs it.

Thank you,

Sciomac

[Nathaniel_C]

Hi @sciomac ,
If you would copy and paste your data directly into your post, instead of pictures, we can do the same into Power Query and perhaps solve your issue.
Thank you,

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[sciomac]

rThank you for the tip @Nathaniel_C . Here the data

ID	Worker_ID	Date	Time	Type	Order ID
7690	11	01/12/2017	7:39:25	Start time	1617
7691	11	01/12/2017	9:26:34	Endtime	1617
7692	6	01/12/2017	10:14:50	Start time	1616
7693	6	01/12/2017	10:15:36	Endtime	1616
7694	6	01/12/2017	10:16:15	Start time	1617
7695	6	01/12/2017	14:26:52	Endtime	1617
7696	6	01/12/2017	14:27:22	Start time	1620
7697	11	01/12/2017	15:33:36	Start time	1606
7698	11	01/12/2017	15:33:49	Endtime	1606
7699	11	01/12/2017	15:33:56	Start time	1607
7700	11	01/12/2017	15:34:06	Endtime	1607
7701	6	01/12/2017	15:37:15	Endtime	1620
7702	11	04/12/2017	9:20:47	Start time	1617
7703	11	04/12/2017	9:21:00	Endtime	1617
7704	11	04/12/2017	9:57:49	Start time	1605
7705	11	04/12/2017	9:57:55	Start time	1604
7706	11	04/12/2017	10:26:51	Endtime	1604
7707	11	04/12/2017	10:27:24	Endtime	1605
7708	6	04/12/2017	15:40:58	Start time	1620
7709	6	04/12/2017	15:41:20	Endtime	1620
7710	6	05/12/2017	9:39:54	Start time	1620
7711	6	05/12/2017	9:40:12	Endtime	1620
7712	6	05/12/2017	9:40:28	Start time	1621
7713	11	05/12/2017	9:50:58	Start time	1628
7714	11	05/12/2017	9:51:07	Endtime	1628
7715	6	05/12/2017	13:11:28	Endtime	1621

[Nathaniel_C]

Hi @sciomac ,
Thank you, loading and working on it.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @sciomac 

How does this look?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdS9boMwEADgV4mYI+XO9vnAczp06tBKHVBURQpDVTWtEEvfvkeIobh3LDEEfblf0rbV47HaV69f/UfXv92uj+ehk+Pl/fN2/HyPx1N/6fqdPD/t24pjA/IdonwAHtAdHCDLDSffJEdy9Tyc+2E3TL+BUZ7eIapQVEw+yNXD9aIoJ/exRAgJQyJQwsUMvQUp+ViGm1WwVBS4VR2pMIzVkTOri5bi5Nz/cA4yZLWZY21+Kq+QMFdYb8jQFJkurNlgpAW818igL8woQ4JyEgtDtTWieJrEX5X7wuDmYGG9ZJAC2/Nj8BbEdKtAmx9DMBTx1MuyKZQh2ZCUPZNAdxhVKBs6LhqW3ZwZm0w2rXz7ljTrPISwHkKARLW9nwyNBVFGYU0PIStaNUX+WihsREPUnWSJ5bu3IGcip5WG2S2bsoZk9KTOMFhQVozLNGdFWproE+KU50pJkqdf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Worker_ID", Int64.Type}, {"Date", type date}, {"Time", type time}, {"Type", type text}, {"Order ID", Int64.Type}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Worker_ID", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type2", each ([Type] = "Start time")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Time", "Start time"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Type"}),
    #"Appended Query" = Table.Combine({#"Removed Columns", Endtime}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"ID", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"ID", "Worker_ID", "Date", "Start time", "Endtime", "Order ID"}),
    #"Filled Up" = Table.FillUp(#"Reordered Columns",{"Endtime"}),
    #"Sorted Rows1" = Table.Sort(#"Filled Up",{{"Order ID", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows1", each ([Start time] <> null)),
    #"Sorted Rows2" = Table.Sort(#"Filtered Rows1",{{"ID", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows2",{"ID"})
in
    #"Removed Columns1"

Will post pbix soon.