Forum Discussion
Filtering Away Certain Duplicate Values in Pivot Data Source
Hello,
I'm using Pivot Table and Slicer to filter data from a raw data source. Our employees work rotating shifts. Between those shifts there are overlap hours. I created a file to track their performance during those 9 hours of shifts.
Morning shift: 08:00 - 17:00
Mid Shift : 16:00 - 01:00
Night Shift : 23:30 - 08:30
I'm using Slicer to select between shifts.
Our tool provides the raw data I need without separating shifts. So for overlap hours (16:00 - 17:00) we get duplicate data resulting in employees appearing in both morning and mid shift in the Pivot Table. What I want is to Excel to ignore the second data set for duplicate entries and not include them in the pivot. Is this possible? Or is there any other way?
Am I clear enough? If not I can provide more info.
I'm attaching the excel file I'm working on for you to see what I'm doing.
Changed the Name 1 to Name01
Sorted the dataset by Name and No. of Initial Moderation Tasks
created filter columns rank, count, subnum, ref
did a power query on dataset including filter columns
created 2 reference queries Top 2 and Bottoms queries
filtered out bottoms on Top 2 reference query
filtered out Top2 on Bottoms reference query
cheers
17 Replies
Not with your dataset since it cannot actually tell which employee belongs to which actual shift, i.e Employee1 might belong to the morning shift most hours and Employee2 might belong to the mid shift most hours but because of the overlap your dataset only indicates which employees are in overlapping shifts but not actually indicate which actual shift the belong to.
Thank you for answering. I edited the file including shifts for each employee and made it more concise and readable. Including only 1 set of data for 1 day. Also someone else suggested me using a helper to identify overlap hours and exclude them using slicer but It's not working as intended. It's not my formula.
