Power Query: Search in previous rows

[fbackes]

I have the following input from a text file:

11,1,11,DT:17.09.18/13:04:30
11,2,12,NR:5963
11,3,13,NR:102
11,4,21,NR:200170006
11,4,23,NR:200170000
11,4,24,TX:'1'
11,4,31,CA:1/3.5
11,5,11,DT:17.09.18/16:53:13
11,6,12,NR:5423
11,7,13,NR:5
11,8,21,NR:200010002
11,8,23,NR:200010000
11,8,24,TX:'1'
11,8,31,CA:1/3.8

Column 1: constant (11)

Column 2: consecutive counter

Column 3: row type (e.g. 11 = Date; 12: Key for person; 21: product key; 23: product group key; 31: number of units and revenue 

The rows with types 11 always embed a unit of transaction and I'd like to create a new column, which holds an identifier for the transaction. This could be the counter (Col 2) of the date row (Col 3 = 11).

That means, for each row, the value of column 2 of the last previous row of type 11 has to be found. In the above example, the new column would hold 1 for the first seven rows and 5 for the last seven rows.

That's quite easy in DAX, but I'd like to do it on the Power Query level in M.

Any ideas?

Thanks ind advance
Frank

[zoloturu · Accepted solution]

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!

[fbackes]

Many thanks, Ruslan! It works perfect.

Best regards
Frank