Calculate time between dates on 2 different rows

I ve created this table with kind of same structure.

Then Group By ID

Then add a column with the "add personalized column"

Paste this inside

= Table.AddColumn(#"Lignes groupées", "Personnalisé", each Table.FillDown([Nombre],{"End date"}))

explanation

Delete column [Nombre]

expand [Personnalisé]

Then add logic 🙂

Then append tables.

You can also try to keep file name by type of selected source (ex: folder)

Hi, Patrick95 ;

You could try it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLStdA1NFAwNLYytLAyMAAKKsXqQGTR2DDFhgqGhlZGliDFIFknJClTBUNTK2NTJHOcUPUCFZhZGYAtio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DocumentNo = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DocumentNo", type text}, {"Start", type datetime}, {"End", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DocumentNo"}, {{"Count", (x)=> Table.AddColumn(x, "Picking Time", each Duration.ToRecord(List.Max(x[End]) - List.Min(x[Start])), Int64.Type)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Start", "End", "Picking Time"}, {"Start", "End", "Picking Time"}),
    #"Expanded Picking Time" = Table.ExpandRecordColumn(#"Expanded Count", "Picking Time", {"Days", "Hours", "Minutes", "Seconds"}, {"Days", "Hours", "Minutes", "Seconds"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Picking Time",{{"Hours", type text}, {"Minutes", type text}, {"Seconds", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","0","00",Replacer.ReplaceValue,{"Hours", "Minutes", "Seconds"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value", "duration", each Text.Combine({Text.From([Days], "zh-CN"), Text.From([Hours], "zh-CN"), Text.From([Minutes], "zh-CN"), Text.From([Seconds], "zh-CN")}, ":"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Merged Column",{"Hours", "Minutes", "Seconds"})
in
    #"Removed Columns"

The final show:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.