Table.AddColumn help - adding more than one previous row column

Hi JanoLehocky ,

You can try this query to get the previous row value in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZRNT4NAEIb/yoRzD9BKhaO1msbUj7QkHpoeRpjYDQvbLLs2/HsHPChQEzTBVg6b5d152Hdn2NlsnMlk4oycmRbGYF7Cek+oC1bWKFELSnjquTzcE4fAit4EHZztqB/o8fCkVaZqIggCfr+RUihj4FpJyjBvEg8VgCUUMUoCkceasKC/ox/NjvSgwDf+PNd163TamGBJxPMra9QeRQILZbUsWfkR/rUO40YdegR3qh16Psu3WB0XIpG1oel/hMKQ5YV93cEzyRfOcqteXtCocL/4896miwVhraNOITqgaEPHMzcEdHliqH545c4WRuQQaZsQ2hZ68Xu0apdzypQRKv+4h+PqayuVsABLm+IRm5+/RZ/ojrMz2WJgX9N2Zn2/agdzQYbbwUxinvYxdXKq2/H9Drt9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personnel no." = _t, #"Personnel Number" = _t, #"EE subgroup name" = _t, #"Salary Band" = _t, #"Reason for Changing Master Dat" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Personnel no.", Int64.Type}, {"Personnel Number", type text}, {"EE subgroup name", type text}, {"Salary Band", type text}, {"Reason for Changing Master Dat", type text}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type", {"Personnel no."}, 
            {
                {
                    "Data", each 
                    let tab=Table.AddIndexColumn(_,"Index",1,1,Int64.Type) in
                        Table.AddColumn(
                            tab,"New",
                            (x)=> try Table.Max(Table.SelectRows(tab,(y)=>y[Index]=x[Index]-1),"Index")[EE subgroup name]
                                    otherwise null ), 
                type table [#"Personnel no."=nullable number, Personnel Number=nullable text, EE subgroup name=nullable text, Salary Band=nullable text, Reason for Changing Master Dat=nullable text]
                }
            }
        )
in
    #"Grouped Rows"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.