cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MIchri
Resolver I
Resolver I

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.

Udklip.JPG

 

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: 

Udklip2.JPG

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
Udklip3.JPG

Thanks!,

Mikkel

2 REPLIES 2
Anonymous
Not applicable

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

 

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? 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors