Hi Chris,

Thanks for the file, but I'm not able to quickly understand how you're arriving at your desired result. Please provide further clear explanation so I can easily understand and follow along.

Thanks,

Mynda

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 !

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

Hi Mynda,  I am sorry if the sample data didn't reflect what I just explained,   by the way will duplicating three sets slow down the query generation also ?  as my actual dataset overtime may be about 500,000 records

Thank you !

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 ! 

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

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

If you don't need to ignore weekends then it's even easier. Just create a date table of consecutive dates. Merge the tables based on the date column and continue with the steps from my original example by duplicating the query two more times and adding indexes etc.

Mynda