Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

time difference between 2 rows

Hi

 

who can help me?

 

I have a table like this:

 

ID | EMPLOYEE | |IN/OUT | Date | Time
1    1002        IN       2017-01-01 08:00
2    ......
5    1002        OUT      2017-01-01 18:00
6    ......

 

 

Now i want to know the attendance time for the EMPLOYEE  keeping in mind that there a more rows for another people

 

It also can happen that people works in a nightshift

 

 

ID | EMPLOYEE | |IN/OUT | Date | Time
1    1002        IN       2017-01-01 22:00
2    .....
5    1002        OUT      2017-01-02 07:00
6    .....

 

Also then i want to know the time

 

Wiht kind regards,

 

Norbertus

 

 

Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    In Power Query you can:

    1. Combine Date and Time to DateTime.
    2. Sort on employee and DateTime.
    3. Add 2 indices starting with 0 and 1.
    4. Merge the table with itself using the first and second index as key, so you have the previous values on the same row as the current values.
    5. Add a column with the DateTime minus the previous DateTime if the employee is the same and the previous was IN and the current is OUT, else null.
    6. Sort on ID to restore the original sort (not really necessary).
    7. Remove columns that are no longer needed.
    let
    Source = Table1,
    #"Inserted Merged Date and Time" = Table.AddColumn(Source, "DateTime", each [Date] & [Time], type datetime),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Date and Time",{{"EMPLOYEE", Order.Ascending}, {"DateTime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Prev",JoinKind.LeftOuter),
    #"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"EMPLOYEE", "IN/OUT", "DateTime"}, {"Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Prev", "TimeDifference", each if [EMPLOYEE] = [Prev.EMPLOYEE] and [#"Prev.IN/OUT"] = "IN" and [#"IN/OUT"] = "OUT" then [DateTime] - [Prev.DateTime] else null, type nullable duration),
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"ID", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"DateTime", "Index", "Index.1", "Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"})
in
    #"Removed Columns"

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

     

    You can refer to below formula to achieve your requirement.

    Table:

     

    Measure:

    diff = 
var current_user=LASTNONBLANK(Record[User],[User])
var current_date=MAX(Record[DateTime])
var current_state=LASTNONBLANK(Record[Actio],[Actio])
var min_date=SWITCH(current_state,"Login",current_date,"Logoff",MAXX(FILTER(ALL(Record),[User]=current_user&&[DateTime]<current_date),[DateTime]))
var max_date=SWITCH(current_state,"Login",MINX(FILTER(ALL(Record),[User]=current_user&&[DateTime]>current_date),[DateTime]),"Logoff",current_date)
return
DATEDIFF(min_date,max_date,MINUTE)

    Result:

     

    Notice: datetime are merged by date and time column, 'user' = 'employee', 'action' = 'in/out'.

     

     

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      what if we have more than 1 login and log off for the same employee in the same day, so u have any solution for that

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        help please as for the same employee i have more than login and log off for the same day and I need to calculate networking hours ( latest exit time - earliest entry time  ) for same-day and same employee 

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    wondering if its possible that there are more ins and outs for one person, or a little rephrased. Your are looking for the duration a a person has been in when leaving (out), whereas this duration is the difference between the in and its immediate following out.

    Right?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      hi,

       

      There are more Employees so i don't know which row is the OUT time for one specific Employee.
      I need to know the time between IN and OUT for one Employee

       

      • MarcelBeug's avatar
        MarcelBeug
        Community Champion
        9 years ago

        In Power Query you can:

        1. Combine Date and Time to DateTime.
        2. Sort on employee and DateTime.
        3. Add 2 indices starting with 0 and 1.
        4. Merge the table with itself using the first and second index as key, so you have the previous values on the same row as the current values.
        5. Add a column with the DateTime minus the previous DateTime if the employee is the same and the previous was IN and the current is OUT, else null.
        6. Sort on ID to restore the original sort (not really necessary).
        7. Remove columns that are no longer needed.
        let
    Source = Table1,
    #"Inserted Merged Date and Time" = Table.AddColumn(Source, "DateTime", each [Date] & [Time], type datetime),
    #"Sorted Rows" = Table.Sort(#"Inserted Merged Date and Time",{{"EMPLOYEE", Order.Ascending}, {"DateTime", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Prev",JoinKind.LeftOuter),
    #"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"EMPLOYEE", "IN/OUT", "DateTime"}, {"Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Prev", "TimeDifference", each if [EMPLOYEE] = [Prev.EMPLOYEE] and [#"Prev.IN/OUT"] = "IN" and [#"IN/OUT"] = "OUT" then [DateTime] - [Prev.DateTime] else null, type nullable duration),
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"ID", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"DateTime", "Index", "Index.1", "Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"})
in
    #"Removed Columns"