Impossible Data Transformation

[Anonymous]

Hi folks,

I have a curvy one and can't even figure out on paper how to transform this so here is hoping:

I've got a table which contains swipecard information per employee. My goal is to identify how many hours were spent at a site/location for each employee and then roll it up at various levels.

The problem that I have is that one employee may do multiple swipes a day, at the entrance and throughout the locations. I am able to identify when a swipe is done at an entrance/exit through the relevant columns but have the following problems:

  . an employee may swipe to come in (ie to let someone in) or go out multiple times in a row

  . an employee may not swipe in as he was let in or may not swipe out (in this event, I would assume the time spent at site is 8 hours)

  . an employee may work night and start in the evening and finish in the morning.

I am not able to attach a pbi file (am on company computer and can't load my personal OneDrive) but below is an extract the data. I assume I need to transform and transpose the data against a date table but have no idea where to start.

Company	Card #	SwipeDateTime	Swipe Point	Main Entry Flag	Main Exit Flag
A	712	1/01/2019 8:00	RANDOM	YES	YES
A	712	1/01/2019 10:00	RANDOM	YES	NO
A	712	1/01/2019 18:00	RANDOM	NO	YES
A	8216	3/01/2019 8:00	RANDOM	YES	NO
A	72	3/01/2019 9:00	RANDOM	YES	NO
A	8216	3/01/2019 10:00	RANDOM	NO	NO
A	8216	3/01/2019 19:00	RANDOM	NO	YES
A	72	4/01/2019 2:00	RANDOM	YES	NO
B	8783	5/01/2019 8:00	RANDOM	YES	NO
B	8783	5/01/2019 8:01	RANDOM	YES	NO
B	8783	5/01/2019 11:00	RANDOM	NO	YES
B	2836	6/01/2019 22:00	RANDOM	YES	NO
B	2836	7/01/2019 1:00	RANDOM	NO	NO
B	2836	7/01/2019 2:00	RANDOM	NO	NO
B	2836	7/01/2019 4:00	RANDOM	NO	NO
B	2836	7/01/2019 6:00	RANDOM	NO	YES

Any thoughts or guidance will be greatly appreciated 🙂

OF

[vanessafvg · Accepted solution]

@Anonymous  what are your requirements to get the first and last?  what flags are you using?  you need to give a bit more information about the rules you want to develop.

Do you want the first and last for each day, how do you know if they worked over night?  etc..

[Anonymous]

@vanessafvg, apologies, I incorrectly marked this as solved.

I'm trying to calculate the numbers of hour per employee, per day, spent at site (if they worked overnight, just number of hours spent there from one evening to th enext morning). The two "tricky parts" are:

. an employee may swipe in twice (only the first one should count)

. an employee may not swipe in and only swipe out, in which case we can assume the swipe in was 8 hours earlier.

Did I explain this correctly?

[vanessafvg]

@Anonymous  dd you get this resolved?