Need Help Running Index that Resets

[Anonymous]

Running Index that Resets based on criteria 

Hello i need help with this

I have a Table with many variables, that have Values, this value are evaluated as Status 1 or 0

where 0 = Ok, and 1 is Out of Specification

This variables also have a Criteria for Anomaly Analysis

So i have to make a colum that Count consecutive Status Events that are out of specification

When the counter number reach the same value as the criteria, then it has to show a value 1 in Anomaly Analysis

MONTH	DAY	HOUR	VARIABLE	VALUE	STATUS	CRITERIA	COUNTER	ANOMALY ANALYSIS
abril	30	00:00	Acidez en OFLA2	123	1	6	1
abril	30	04:00	Acidez en OFLA2	123	1	6	2
abril	30	08:00	Acidez en OFLA2	137	1	6	3
abril	30	12:00	Acidez en OFLA2	121	1	6	4
abril	30	16:00	Acidez en OFLA2	125	1	6	5
abril	30	20:00	Acidez en OFLA2	128	1	6	6	IA
mayo	1	00:00	Acidez en OFLA2	123	1	6	7
mayo	1	04:00	Acidez en OFLA2	129	1	6	8
mayo	1	08:00	Acidez en OFLA2	128	1	6	9
mayo	1	18:00	Acidez OF I2000	10	1	6	1
mayo	2	10:00	Acidez OF I2000	2.00	1	6	2
mayo	2	14:00	Acidez OF I2000	2.00	1	6	3
mayo	2	18:00	Acidez OF I2000	1.00	1	6	4
mayo	2	22:00	Acidez OF I2000	1.00	1	6	5
mayo	3	02:00	Acidez OF I2000	1.00	1	6	6	IA
mayo	3	06:00	Acidez OF I2000	2.00	1	6	7

[wdx223_Daniel]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkwqysxR0lEyNgASBlYGIMoxOTMltUohNU/B383H0QgoYmhkDCKB2EwpVgdNlwlZuixw6jI2x63L0Ai3ZYZ4tJnh1maKW5sRngCxQNGWm1iZDxUgOhCR9OAJQktcenAHIG63GaJq8ndT8DQyAIsYGmDTAzbOAJceI5xaTEjXgttluLQYGZGkBRT4pOswI8InsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MONTH = _t, DAY = _t, HOUR = _t, #" VARIABLE" = _t, VALUE = _t, STATUS = _t, CRITERIA = _t]),
    Custom1 = Table.TransformColumns(Source,{{"STATUS",Number.From},{"CRITERIA",Number.From}}),
    Custom2 = #table(Table.ColumnNames(Source)&{"COUNTER","ANOMALY ANALYSIS"},List.Accumulate(Table.ToRows(Custom1),{},(x,y)=>let a=List.Last(x,{}) in if y{3}=a{3}? then x&{y&{a{7}+1??1,if a{7}+1=y{6} then "IA" else null}} else x&{y&{if y{5}=0 then null else 1,null}}))
in
    Custom2

[Anonymous]

Like this?

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {" VARIABLE"}, {{"Details", each _, type table [MONTH=nullable text, DAY=nullable number, HOUR=nullable number, #" VARIABLE"=nullable text, VALUE=nullable number, STATUS=nullable number, CRITERIA=nullable number]}}),
Custom1 = Table.TransformColumns(#"Grouped Rows", {{"Details", each Table.AddIndexColumn(_, "Index", 1,1)}}),
#"Expanded Details" = Table.ExpandTableColumn(Custom1, "Details", {"MONTH", "DAY", "HOUR", " VARIABLE", "VALUE", "STATUS", "CRITERIA", "Index"}, {"MONTH", "DAY", "HOUR", " VARIABLE.1", "VALUE", "STATUS", "CRITERIA", "Index"})
in
#"Expanded Details"

--Nate

[Anonymous]

Hello, thanks for the help, i think we aproach to the objetive but still i need to make the counter reset every time the consecutive Status change, 

Actually the counter is reseting when the Variable change, but it should also resets when the Status change, i attach an image of the Counter-ToBe

[Anonymous]

I wast no very clear in this case, the column COUNTER, is the one that i dont know how to make,

If the Status is 1, The COUNTER has to increase from 1 until appear a row with Status 0, then it has to reset the counter, and start from 1 again.

[Anonymous]

In the formula bar, type:

= Table.AddColumn(SourceOrTableName, "ANOMALY ANALYSIS", each if [VALUE STATUS] = [CRITERIA STATUS] then 1 else null, type number)

--Nate