Forum Discussion
Returning unique values (first login, last logout) based on date-rules in Power Query
Hi
I have this table where I want it to give me unique values of MemberName who has been on duty. Unfortunately, some members do make mistakes when loggin in or out. Example below John who log in at 07:50 on the 2nd of nov by mistake and then logs out 6 minutes later. He later logs in correctly the same day but at 20:00. I want it to give me logged in at 20:00 and logged out at 08:07.
At the moment I use this formula to find the value where members logged in and logged out. But with mistakes like the ones above this doesnt work:
= Table.Group(#"Added Custom", {"ResourceID", "InvoicingDate", "ShiftName", "CrewMemberId", "CrewMemberName"}, {{"FørsteLogin", each List.Min([CrewMemberSignOnTime]), type datetime}, {"SidsteLogout", each List.Max([CrewMemberSignOffTime]), type datetime}})
It returns:
And I want it to be like this: where mistakes, which is to be determinded by a login and logout with in 30 min are taken into account in the formula
Thanks!,
Mikkel
2 Replies
Anonymous Please create a measure in your table to check if duration is greater then 30 minutes
Measure = VAR _login = MAX('Table'[TimeOfLogin]) VAR _logout = MAX('Table'[TimeOfLogout]) VAR _duration = DATEDIFF(_login,_logout,MINUTE) RETURN IF(_duration>30,TRUE(),FALSE())Now go to modeling tab and create a table
Final table = FILTER('Table',[Measure]=TRUE())