Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.