Calculate time difference across dates and users

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.