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

Employee Shift Grouping

Here's the problem I am trying to solve with Power Query. As a caveat, I am very new at using this tool but I think that it should be able to help me solve this problem.

 

We have many employees that work for many different clients and their shift data is recorded in our time and attendance system. We have a policy that these employees are not allowed to work more than 16 hours in one shift. In order to monitor this, we have been pulling a report from the system and filtering out any **bleep** that was over 16 hours.

 

I just recently found out that this wasn't alerting us to all shifts, specifically shifts that span two days. How our timekeeping system handles this is after the shift is clocked out the original Punch ID is canceled and 2 new Punch IDs are created for the amount of time clocked in on each separate day while referencing the original Punch ID in the Reference Punch ID field. So for this one shift there are now 3 different Punch IDs, two of which have the Reference Punch ID field populated.

Shifts that I would like to show up in the query

  1. Any shift that is Approved or Pending and over 16 in the amount column
  2. Any overnight shift that is Canceled over 16 hours as well as the 2 new Approved shifts that were created from the split that is usually under 16 Hours
    1. Ideally, these are grouped together with the Original Punch ID on the top followed by any Punch IDs that have the Original Punch ID in the Reference Punch ID Column

Using Power Query I removed all unnecessary columns to get the dataset to the one below. The Punches that get split Punch IDs are greater than the original Punch ID. 

Punch ID

Reference Punch ID

Date Of Service

Employee

Client

Cost Center

Hours

Status

418569

 

10/1/2020

Susan Caregiver

Bob Consumer

MJ

17

Canceled

418596

418569

10/1/2020

Susan Caregiver

Bob Consumer

MJ

10

Approved

418597

418569

10/2/2020

Susan Caregiver

Bob Consumer

MJ

7

Approved

4185995

 

10/3/2020

Debby Caregiver

John Consumer

MP

18

Approved

4185999

 

10/7/2020

Debby Caregiver

John Consumer

MP

19

Pending

Where I am struggling with Power Query is Filtering out the shifts over 16 while still keeping the shifts that are lower than 16 but reference a shift that is over 16 and then grouping those together. I have tried using some helper columns and doing some lookups but I just can't seem to get them to group right.

 

I know this was long-winded and I appreciate you reading this far, but I tried my best to not leave anything out. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Hello @CoreyB76 

 

you can add new column to check if an ID was split or was split. WIth this parameter you can filter your table (not splitted canceled) and make the check >16 only on rows that were not splitted. Here a practicable example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjG0MDWzVNJRAiJDAz0DQz0jAyMDICe4tDgxT8E5sSg1PbMstQgo4pSfpOCcn1dcmgvm+nqBtJgDCefEvOTUnNQUpVgdiIGWZkBRuMnkGAtS6lhQUJRfhmysObqxRqQZa47VVEtThPeNYQa6pCYlVaIY6JWfkYdiYgBIjwV2I5FC1JxUI0F6A1LzUjLz0pViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Punch ID" = _t, #"Reference Punch ID" = _t, #"Date Of Service" = _t, Employee = _t, Client = _t, #"Cost Center" = _t, Hours = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Punch ID", Int64.Type}, {"Reference Punch ID", Int64.Type}, {"Date Of Service", Int64.Type}, {"Employee", type text}, {"Client", type text}, {"Cost Center", type text}, {"Hours", Int64.Type}, {"Status", type text}}),
    AddIsSplit = Table.AddColumn
    (
        #"Changed Type",
        "IsSplit",
        each List.Contains(#"Changed Type"[Reference Punch ID], [Punch ID]),
        type logical
    ),
    FilterCancelNotSplitted = Table.SelectRows
    (
        AddIsSplit,
        each not ([Status]="Canceled" and [IsSplit]=false)
    ),
    AddWasSplit = Table.AddColumn
    (
        FilterCancelNotSplitted,
        "WasSplit",
        each List.Contains(AddIsSplit[Punch ID], [Reference Punch ID]),
        type logical
    ),
    Over16 = Table.AddColumn
    (
        AddWasSplit,
        "Over16",
        each if [WasSplit]= false and [Hours]>16 then true else false,
        type logical
    )
in
    Over16

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

1 REPLY 1
Highlighted
Super User II
Super User II

Hello @CoreyB76 

 

you can add new column to check if an ID was split or was split. WIth this parameter you can filter your table (not splitted canceled) and make the check >16 only on rows that were not splitted. Here a practicable example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjG0MDWzVNJRAiJDAz0DQz0jAyMDICe4tDgxT8E5sSg1PbMstQgo4pSfpOCcn1dcmgvm+nqBtJgDCefEvOTUnNQUpVgdiIGWZkBRuMnkGAtS6lhQUJRfhmysObqxRqQZa47VVEtThPeNYQa6pCYlVaIY6JWfkYdiYgBIjwV2I5FC1JxUI0F6A1LzUjLz0pViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Punch ID" = _t, #"Reference Punch ID" = _t, #"Date Of Service" = _t, Employee = _t, Client = _t, #"Cost Center" = _t, Hours = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Punch ID", Int64.Type}, {"Reference Punch ID", Int64.Type}, {"Date Of Service", Int64.Type}, {"Employee", type text}, {"Client", type text}, {"Cost Center", type text}, {"Hours", Int64.Type}, {"Status", type text}}),
    AddIsSplit = Table.AddColumn
    (
        #"Changed Type",
        "IsSplit",
        each List.Contains(#"Changed Type"[Reference Punch ID], [Punch ID]),
        type logical
    ),
    FilterCancelNotSplitted = Table.SelectRows
    (
        AddIsSplit,
        each not ([Status]="Canceled" and [IsSplit]=false)
    ),
    AddWasSplit = Table.AddColumn
    (
        FilterCancelNotSplitted,
        "WasSplit",
        each List.Contains(AddIsSplit[Punch ID], [Reference Punch ID]),
        type logical
    ),
    Over16 = Table.AddColumn
    (
        AddWasSplit,
        "Over16",
        each if [WasSplit]= false and [Hours]>16 then true else false,
        type logical
    )
in
    Over16

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors