Inserting previous rows basing upon a condition

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY/dCsIwDEbfpdcjNH9rdunUXYigiDcy9v6vYRoFKyuUQxNO2i/rmijnNCSsYHFcTsdaUwac/DKfH2kb/jQ0x212qIFZX1J0LMvLaQRCO4srPgezCcTA4atwNxSxwlialxqN2HG9P52iI8RURxOtv4RWottxfvsRi0KxnTa12aMTwbc3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Code = _t, Assignment = _t, AssignmentDesc = _t, Cost = _t, Mode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, {"Cost", type number}, {"Mode", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Temp", each _, type table [ID=nullable number, Code=nullable number, Assignment=nullable number, AssignmentDesc=nullable text, Cost=nullable number, Mode=nullable text]}}),
    //Function Start
    fxProcess=(Tbl)=>
        let
            #"Filtered Rows" = Table.SelectRows(Tbl, each ([Mode] = "BER")),
            Custom2 = Table.ReplaceValue(#"Filtered Rows",each [Code],each null,Replacer.ReplaceValue,{"Code"}),
            Custom3 = Table.ReplaceValue(Custom2,each [Cost],each null,Replacer.ReplaceValue,{"Cost"}),
            #"Appended Query" = Table.Combine({Tbl, Custom3}),
            #"Filled Down" = Table.FillDown(#"Appended Query",{"Code"})
        in
    #"Filled Down",
    //Function End
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fxProcess([Temp])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID", "Temp"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"}, {"ID", "Code", "Assignment", "AssignmentDesc", "Cost", "Mode"})
in
    #"Expanded Custom"

Hi,

Rows are inserting accordingly,But value for the column "MODE" is replaced/copied by its previous row value.In our case it should not.
Please see the chart below for more info.

Thanks,

Joyhy

try this:

let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("pdK9CsIwEADgd7m5hPy2abb+WFAURVxEHUSLCLUOsZP47ja1lKBBrW5JyH133N3qCsMUFMXYg+S8z0GR+hRpfTyUp7y8gGLcvqe53oGCUZqACdD1B0IxIqEHkyYc4sEcbt5HlkgHO43XFcZb3NlCIin70oI46CxbdqqkiNNPKntSy6ooXt3Ha9sILDkyyVs4smD2R4MpE8gPnPW+YSlzsOPZomO58JHJ3Y/lwsFGFhuY2H7md7tAGRcocG+DhYc/zM0quRna5g4=", BinaryEncoding.Base64),Compression.Deflate))),
    rows = Table.ToRows(Source),
    acc = List.Accumulate(rows,
              {{}, {}}, 
              (s,c)=>if c{5}<>"AER" then 
                          {s{0}&{List.ReplaceRange(c, 4, 2, {null, "AER"})}, s{1}&{c}} 
                     else 
                          {{}, s{1}&{c}&s{0}}),
    result = #table(Table.ColumnNames(Source), acc{1})
in
    result

Hi,

I am trying to use your given logic on a table which has nearly 50K rows but ran into issues on some of the scenarios which I have not outlined in my earlier post. Now I have updated those scenarios and can you please help me on this. (Expecting output similar to the one outlined in the chart below under the heading "Expected output" ). Please see the chart below for more info.

My requirements are:

1) Take number of rows from where combination of the respective ID & Code is maximum for Assignment & AssignmentDesc is not null for column Mode='BER' and insert those rows for column Mode='AER'.
2) Newly inserted rows for column Mode="AER" should carry Assignment,AssignmentDesc, AllCost column values from their copied/previous rows. Here ALLCost is a "calculated column".
3) Newly inserted rows for column Mode="AER" should have the existing values as it is  for ID, Code, Mode columns and cost value as null/blank.