Forum Discussion
Remove a entry in PowerBi based on next entry
In picture we have a machine RW100 Side A where fail entry created by Operator 10314. The operator was able to fix the late entry with in 5 mins as we see next entry 159. How do I remove the late entry from PowerBI or exclude it when the new entry shows up within 5 mins?
10314 is Operator ID. Minutes is (minutes between created and now) which do not mean anything here.
8 Replies
Hi lotus22
I've done this in Power Query for you.
There are two tables in the attached PBIX. One is filtered (with the > 5 mins remaining). The other keeps both >5 mins and <5 mins in but I've added a validation column for you.
The approach I applied was:
- Create a new Custom Column to show the next entry with the same machine and operator.
- Calculated the time difference between the entries for the above.
- Filtered out entries with a time difference greater than 5 mins.
Let me know if you need this in DAX otherwise the PQ version is attached.
Hope this helps! 🙂
Theo
TheoC Thank you so much! That was quick.
We only need to validate if the previous entry is LATE and the next entry is on time. As soon as we get a Late entry for a particular machine, we must ensure another entry is on time within 5 minutes. If yes, then we delete the late entry; otherwise, it shows up.
For instance, if 158, a late entry, appears and we know that 159, a non-late entry, follows within 5 minutes, we can 'exclude' 158. Our focus is solely on validating late entries.
It gets tricky. If RW100 Side A has entry 158 that is late but there was no late entry, we include this on the graph. Now, after two hours, if RW100 Side A has another late entry, say 250, that will be a new validation but not part of 158.
