Time spent in each location

I am interested to know the time spent by my staffs at each location.

Any idea to calculate the duration of each Departure - Arrival below?

Here is how the table looks like:

Here is the result I want in PowerBI:

Excel file

Thanks

Need Help

dufoq3

2 years ago

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

4 Replies

dufoq3

Community Champion

2 years ago

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

Greg_Deckler
Community Champion
2 years ago
jaysoulz See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
( __Current - __Previous ) * 1.

For a more specific answer, please post data as text in a table.
jaysoulz
Helper I
2 years ago
Thanks Greg for the article.

Correct me if I am wrong, but in order to facilitate this task, I believe the first step is to unpivot my table first so I can have the columns Departure and Arrival like in your article (RepairStarted and RepairCompleted).

The problem here is when I try to unpivot the TriggerName with Time in PowerBI, I got the following:

The result should be like this (each border represents each day):

What did I do wrong?

Here is the files:
Fabric

Thanks
jaysoulz
Helper I
2 years ago
Thanks for the solution. It is working, but with errors...

I have been trying different way including creating a calculate column, but it failed due to memory issue.

Forum Discussion

Time spent in each location

4 Replies

Related content

Calculate Time SPent

Calculating TIme Spent for each status / stage

Calculate total time someone spent playing a game

Calculation of time spent

Time spent in programm - measure