Time spent in each location

Hi jaysoulz, check this.

Just change file addres in Source step.

Result

let
    Source = Excel.Workbook(File.Contents("c:\Downloads\PowerQueryForum\jaysoulz\REPORT_FABRIC.xlsx"), null, true),
    DATA_Sheet = Source{[Item="DATA",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(DATA_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"FirstName", "LastName", "UserID", "Location", "LocationID", "Type", "Time", "TriggerName"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"FirstName", type text}, {"LastName", type text}, {"UserID", type text}, {"Location", type text}, {"LocationID", type text}, {"Type", type text}, {"Time", type datetime}, {"TriggerName", type text}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([Time]), type date),
    #"Added Index" = Table.AddIndexColumn(#"Inserted Date", "Index", 0, 1, Int64.Type),

    fn_Duration =
        (myTable as table)=>
        let
            // Detail = #"Grouped Rows"{[UserID="b7E6",LocationID="b448A9",Date=#date(2024, 6, 11)]}[All],
            Detail = myTable,
            #"Removed Other Columns1" = Table.SelectColumns(Detail,{"Time", "TriggerName", "Index"}),
            Buffered = Table.Buffer(#"Removed Other Columns1"),
            GeneratedDuration = List.Generate(
                ()=> [ x = 0, y = Buffered{x}[TriggerName], z = Buffered{x}[Index], w = Buffered{x}[Time], result = null ],
                each [x] < Table.RowCount(Buffered),
                each [ x = [x]+1, y = Buffered{x}[TriggerName], z = Buffered{x}[Index], w = Buffered{x}[Time],
                    result = if z = [z]+1 and (y = "Departure" and [y] = "Arrival") then w - [w] else null],
                each [Duration = [result], TotalSeconds = Duration.TotalSeconds([result])]
        ),
            ToTable = Table.FromRecords(GeneratedDuration, type table[Duration=duration, TotalSeconds=Int16.Type]),
            MergedTables = Table.FromColumns(Table.ToColumns(Detail) & Table.ToColumns(ToTable), Value.Type(Detail & ToTable))
        in
            MergedTables,

    GroupedRows = Table.Group(#"Added Index", {"UserID", "LocationID", "Date"}, {{"All", fn_Duration, type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    SortedRows = Table.Sort(CombinedAll,{{"Index", Order.Ascending}})
    in
        SortedRows