Power Query: Search in previous rows

Hi fbackes,

I think I found a solution for you.

1. Add index column

2. Calculate transaction ID per each row as per below code (see Result step):

let
    //Entered your data
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lECY0OlWB0o3wjEN0LwjUF8YwTfBKTGEI2PLm+CyjdGUm+KZp8Zmn3maPZZoNlngWafBZp9FlD7YgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [constant = _t, #"consecutive counter" = _t, #"row type" = _t]),
    ChangedDataTypes = Table.TransformColumnTypes(Source,{{"constant", Int64.Type}, {"consecutive counter", Int64.Type}, {"row type", Int64.Type}}),
    //Add index starting from zero
    AddIndexColumn = Table.AddIndexColumn(ChangedDataTypes, "Row", 0, 1),
    //Calculate transaction id
    Result = 
            Table.AddColumn(AddIndexColumn, "TransactionID", 
                each 
                    let 
                        Ix = [Row],
                        value = List.Last(Table.SelectRows(AddIndexColumn,each [Row]<=Ix and [row type] = 11)[consecutive counter])
                    in 
                        value
                        )
in
    Result

Regards,
Ruslan
-------------------------------------------------------------------
Did I answer your question? Mark my post as a solution!