Forum Discussion

cgkas's avatar
cgkas
Helper V
3 years ago
Solved

How to compare 3 consecutive rows?

Hi to all,

 

I have a column for which I want to compare current row (Nth), the previous row (Nth-1) and next row (Nth+1) in this way.

 

 *  If Nth value > Nth-1 And Nth value < Nth+1 then "Yes" else "No"

 

Is possible to do this in a single step using for example Table.Transform(), List.Accumulate() or something like that without several helper columns?

 

The input is like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgaTpmASImIAJs3BpKGlUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type number}})
in
    #"Changed Type"

 

 

In image below, the input and output looks like this

 

. Thanks for any help.

 

 

Need Help
  • danextian's avatar
    danextian
    3 years ago

    Hi cgkas ,

     

    try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgaTpmASImIAJs3BpKGlUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Output", each // If Nth value > Nth-1 And Nth value < Nth+1 then "Yes" else "No"

let 
//assuming that the nanme of the previously applied step is Added Index otherwise change accordingly
prev = #"Added Index"[Input]{[Index]-1},
next = #"Added Index"[Input]{[Index]+1},
witherror =  if [Input] > prev and  [Input] < next then "Yes" else "No"
//will return an error if before or after row does not exist, if error return "No"
in try witherror otherwise "No", type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

     

4 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    Hi cgkas ,

     

    try this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlKK1YlWMgaTpmASImIAJs3BpKGlUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Output", each // If Nth value > Nth-1 And Nth value < Nth+1 then "Yes" else "No"

let 
//assuming that the nanme of the previously applied step is Added Index otherwise change accordingly
prev = #"Added Index"[Input]{[Index]-1},
next = #"Added Index"[Input]{[Index]+1},
witherror =  if [Input] > prev and  [Input] < next then "Yes" else "No"
//will return an error if before or after row does not exist, if error return "No"
in try witherror otherwise "No", type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

     

    • cgkas's avatar
      cgkas
      Helper V
      3 years ago

      Hi danextian it works pretty fine, even I still like to know if possible to do it without helper columns. Thank you.

       

      I see that after the "each" you inserted a block "let .. in ..". I didn't know that is possible. Without that "let... in..." wouldn't be possible to make the comparison you did?

      • danextian's avatar
        danextian
        Super User
        3 years ago

        try this as a custom column but take note that this approach still uses a helper column which is  Index to get the position of the  current, previous and next rows

         

        try (if [Input] > #"Added Index"[Input]{[Index]-1} and  [Input] < #"Added Index"[Input]{[Index]+1} then "Yes" else "No") otherwise "No"