Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Need Help Running Index that Resets

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

 

MONTHDAYHOUR VARIABLEVALUESTATUSCRITERIACOUNTERANOMALY ANALYSIS
abril3000:00Acidez en OFLA2123161 
abril3004:00Acidez en OFLA2123162 
abril3008:00Acidez en OFLA2137163 
abril3012:00Acidez en OFLA2121164 
abril3016:00Acidez en OFLA2125165 
abril3020:00Acidez en OFLA2128166IA
mayo100:00Acidez en OFLA2123167 
mayo104:00Acidez en OFLA2129168 
mayo108:00Acidez en OFLA2128169 
mayo118:00Acidez OF I200010161 
mayo210:00Acidez OF I20002.00162 
mayo214:00Acidez OF I20002.00163 
mayo218:00Acidez OF I20001.00164 
mayo222:00Acidez OF I20001.00165 
mayo302:00Acidez OF I20001.00166IA
mayo306:00Acidez OF I20002.00167 

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    In the formula bar, type:

     

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

     

    --Nate

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

       

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    4 years ago
    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