Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
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
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
Thank you! Simple solution which I should have thought of. Appreciate your help.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |