cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OzzieFrog Member
Member

Impossible Data Transformation

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.

 

CompanyCard #SwipeDateTimeSwipe PointMain Entry FlagMain Exit Flag
A7121/01/2019 8:00RANDOMYESYES
A7121/01/2019 10:00RANDOMYESNO
A7121/01/2019 18:00RANDOMNOYES
A82163/01/2019 8:00RANDOMYESNO
A723/01/2019 9:00RANDOMYESNO
A82163/01/2019 10:00RANDOMNONO
A82163/01/2019 19:00RANDOMNOYES
A724/01/2019 2:00RANDOMYESNO
B87835/01/2019 8:00RANDOMYESNO
B87835/01/2019 8:01RANDOMYESNO
B87835/01/2019 11:00RANDOMNOYES
B28366/01/2019 22:00RANDOMYESNO
B28367/01/2019 1:00RANDOMNONO
B28367/01/2019 2:00RANDOMNONO
B28367/01/2019 4:00RANDOMNONO
B28367/01/2019 6:00RANDOMNOYES

 

Any thoughts or guidance will be greatly appreciated 🙂

 

OF

1 ACCEPTED SOLUTION

Accepted Solutions
vanessafvg Super Contributor
Super Contributor

Re: Impossible Data Transformation

@OzzieFrog  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..


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

3 REPLIES 3
vanessafvg Super Contributor
Super Contributor

Re: Impossible Data Transformation

@OzzieFrog  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..


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

View solution in original post

OzzieFrog Member
Member

Re: Impossible Data Transformation

@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?

Highlighted
vanessafvg Super Contributor
Super Contributor

Re: Impossible Data Transformation

@OzzieFrog  dd you get this resolved?


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)