Returning unique values (first login, last logout) based on date-rules in Power Query

[Anonymous]

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

[Anonymous]

@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())

[Anonymous]

Hi @Anonymous 

What I really were looking for was a formula in M-language or another query. 
Can you make this in power query instead?