August 21, 2019
Hi all,
anyone done checking of consecutive values (1/2/20, 2/2/20, 3/20/20 then 2/2/20, 3/2/20/ 4/2/20..... for e.g.) for fraud entries investigation by users
I tried using #"Added Index" [Date] {[Index] - 1} and it hangs my power query .....
Anyone got better solution
Thank you in advance
August 21, 2019
Hi Mynda, thanks for your reply
Basically we wanted to track staff input their temperature measures into the applications, whether it is diligently using thermometer to take their body temperature and enter the real result into the system, so we wanted a query to sieve out a person who continuously input the same reading, for e.g. 36.5 degree C for consecutive days ( for e.g. 3/4/20, 4/4/20, 5/4/20) and flag out as "need investigation" (this guy/gal may just anyhow input the same reading without physically took their temperature)
I apologize that my sample can't let you understand clearly, so the three columns will be Day1(AM), Day2(AM) and Day(AM), Day1(AM) will be the start day of the 1st Consecutive day
Hope that you can understand
I was thinking using transpose the next two days reading into 2nd and 3rd column using M language, or using previous row (#"Added Index" [Date] {[Index] - 1} ) to layout 2nd day and 3rd day, once I got the three columns can then use custom column to check whether they are all equal reading
made some attempt yesterday however it took forever to compute in the power query editor
Thought of asking PQ experts in this platform for some tips....
and hope my explanation can let you understand ............ if not will try to explain again
thank you !
July 16, 2010
Hi Chris,
Thanks for the clear explanation. Referencing rows is notoriously slow. You can duplicate the query 3 times and insert an index with a starting number for each table offset by 1. Then merge the tables.
To compare the rows, use List.Distinct via the Add Column > Statistics > Count Distinct Values. You can then wrap this result in an IF to return the text; Investigate or OK.
See file attached. Note: the data in your sample is different to your desired outcome.
Mynda
August 21, 2019
Hi Mynda, a brilliant way of using index with different order to create col 2 and col 3
However for 9/2/2020 should be 36.8 0 0 as the next date is not consecutive, so somehow need to have M code to state the condition not to include next two dates if they are not consecutive for from the beginning date, this is what I am trying to figure out how to, merge there sets together can apply to full calendar dates with data (sure have values)
perhaps PQ is not the suitable tool got to use DAX or native excel ?
Thank you !
Date | Name | AM temp | Shift 1.AM temp | Shift 2.AM temp |
3/2/2020 | Andy Ng | 36.8 | 36.8 | 36.8 |
4/2/2020 | Andy Ng | 36.8 | 36.8 | 36.8 |
5/2/2020 | Andy Ng | 36.8 | 36.8 | 36.8 |
6/2/2020 | Andy Ng | 36.8 | 36.8 | 36.8 |
7/2/2020 | Andy Ng | 36.8 | 36.8 | 36.8 |
8/2/2020 | Andy Ng | 36.8 | 36.8 | 36.8 |
9/2/2020 | Andy Ng | 36.8 | 0 | 0 |
14/2/2020 | Andy Ng | 36.8 | 36.8 | 36.8 |
July 16, 2010
Hi Chris,
If your dates are to be consecutive then you expect your employees to work everyday? I suspect you haven't been clear and what you really mean is you want to check week dates?
If so, create a table with all the dates you want to test. Add columns for the 'Day' name and then filter out the days you don't want to track.
Merge the original data with the date table so that you have the temperature data on consecutive dates excluding weekends. This will be the table you duplicate 3 times and continue with the steps in my original example.
There shouldn't be any performance issues in duplicating the tables. If there is then you'd be best to move to a DAX measure.
I hope that helps.
Mynda
August 21, 2019
thanks for your patience Mynda, not necessarily is exlude weekends, some how certain staff forgotten to input their temperature in the system, basically the query just want to sieve out 3 consecutive dates, once in between there is a break, it will take care separately under my another query non submission
ok, i will take a look on the link and see whether can apply it, thank you so much
1 Guest(s)