Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mvgust
Helper III
Helper III

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 IDEmp NameCharge DateAttendance CodeHoursKeep Record?
444555Smith8/5/20UX3Yes
444555Smith8/6/20RW7Yes
443254Johnson8/5/20RW10No
452718Smith9/10/20RW10Yes
452718Smith9/11/20UX10Yes
1 ACCEPTED 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

 

 

 

View solution in original post

4 REPLIES 4
HotChilli
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.