Forum Discussion
Keep only Timecard records which contain specific code
I've got a table with multiple columns of timecard records for employees. Each timecard record has a unique ID. As part of this table we have attendance codes such as regular work, overtime work, vacation time etc. A timecard id can and usually does include more then one attendance code. I'd like to filter my table in query editor to only keep employee timecard records that contain a specific overtime work code, but I still want to see all the attendance codes for that timecard. How can I setup a filter to do this? I thought about a custom column with an IF statement but I can't work out what that if statement would be.
Below is an example. If the timecard ID contains an attendance code record of 'UX', I want all the records for the timecard ID. If a timecard ID does not contain a record with UX, I want to filter it out of the table.
| Timecard ID | Emp Name | Charge Date | Attendance Code | Hours | Keep Record? |
| 444555 | Smith | 8/5/20 | UX | 3 | Yes |
| 444555 | Smith | 8/6/20 | RW | 7 | Yes |
| 443254 | Johnson | 8/5/20 | RW | 10 | No |
| 452718 | Smith | 9/10/20 | RW | 10 | Yes |
| 452718 | Smith | 9/11/20 | UX | 10 | Yes |
Thanks for the data.
Here's a Power Query solution (I didn't know if you wanted DAX or M solution)
In Power Query, duplicate the table.
In the table copy, filter the Attendance Code column for 'UX' (using the column header dropdown)
Remove all the columns except 'Timecard ID'. Right-click the Timecard ID and 'Remove Duplicates' (if required).
You now have a table with a single column of the Timecard IDs that have a UX record.
Using Merge Queries, merge this table with the original table using Inner Join on TimeCard ID.
Delete the unwanted column.
You now have a table with 4 rows.
Good luck
4 Replies
Post a small sample of the data (not a picture) and explain/show the end result desired please
I've modified my question to include an example.
Thanks for the data.
Here's a Power Query solution (I didn't know if you wanted DAX or M solution)
In Power Query, duplicate the table.
In the table copy, filter the Attendance Code column for 'UX' (using the column header dropdown)
Remove all the columns except 'Timecard ID'. Right-click the Timecard ID and 'Remove Duplicates' (if required).
You now have a table with a single column of the Timecard IDs that have a UX record.
Using Merge Queries, merge this table with the original table using Inner Join on TimeCard ID.
Delete the unwanted column.
You now have a table with 4 rows.
Good luck
