Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Norbertus
Helper V
Helper V

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

 

 

1 ACCEPTED SOLUTION

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"

 

Specializing in Power Query Formula Language (M)

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

Hi @Norbertus,

 

You can refer to below formula to achieve your requirement.

Table:

4.PNG

 

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:

5.PNG

 

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

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

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
Super User
Super User

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?



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

 

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"

 

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.