Calculate time difference across dates and users

[Anonymous]

Hey guys, I'm hoping you can assist two desperate bachelor thesis authors!

We have a dataset with sales orders, a date when order was packed and a worker who packed specific sales order. We are interested to find out how much time each packer spends from finishing one order till finish of the next one. This is how the data is organized (Despatch date, packer name):
"28/7/2023 13:11:00" "Georg",
"29/7/2023 12:11:30" "Britney",
"27/7/2023 08:12:15" Georg",
"28/7/2023 10:12:45" "Britney"

Can you provide a formula that calculates the time difference between finishing one order till finishing another for each packer?

[Anonymous]

Hi @Anonymous ,

You can try my solution in Power Query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQN9c3MjAyVjA0tjI0tDIwUNJRck/NL0pXitUBSlvCpY1A0sYgaaeizJK81EqIAnOYAgMLK5AaU1T9COMNQNImpsj6YwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Despatch date" = _t, #"packer name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Despatch date", type text}, {"packer name", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Despatch date", type datetime}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Despatch date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"packer name"}, {{"All Rows", each _, type table [Despatch date=nullable datetime, packer name=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All Rows],"Index",0,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All Rows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Despatch date", "Index"}, {"Despatch date", "Index"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Previous Despatch date", each List.Range(
   #"Expanded Custom"[Despatch date],
   [Index]-1,
   1)),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Previous Despatch date", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Extracted Values", {{"Previous Despatch date", ""}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Errors",{{"Previous Despatch date", type datetime}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type1", "Time Difference", each Duration.ToText([Despatch date]-[Previous Despatch date]))
in
    #"Added Custom2"

You can also download my attachment for more details.

Best Regards,

Stephen Tao

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

[Anonymous]

Thank you for your reply:)

[Anonymous · Accepted solution]

First sort by name, and then by date. Add two index columns, one starting at zero and one starting at one, then left outer join the table to itself on index equals index one. Then change your text date time to an actual date time value, and then just do the row math, specifying that if Name <> Name.1 then 0 else DateTime-DateTime.2

--Nate

--Nate

[Anonymous]

I appreciate your response!