Insert previous rows with calculated columns

If I understand your logic, the following might work

• Group by ID
• Then use a custom function to 
  • Group by Code
  • Determine which Code has the maximum number of rows
  • Use the rows from that code to create the rows to be inserted

Custom Function

//rename fnInsertAERrows

(tbl as table)=>

let
    //assuming only a single AER code
    #"AER Code" = Table.SelectRows(tbl, each [Mode]="AER")[Code]{0},
   
    #"BER Rows" = Table.SelectRows(tbl, each [Mode] = "BER" and [Assignment] <> null),
   
    #"Code Count" = Table.Group(#"BER Rows",{"Code"},{
        {"Count", each Table.RowCount(_), Int64.Type}               
                }),
    #"Code with Max Rows" = Table.SelectRows(#"Code Count", each [Count] = List.Max(#"Code Count"[Count]))[Code],
   
    #"Full Rows to Insert" = Table.SelectRows(#"BER Rows", each List.Contains(#"Code with Max Rows", [Code])),
   
    #"Modified Rows to Insert" = Table.FromRecords(Table.TransformRows(#"Full Rows to Insert", (r) =>
        Record.TransformFields(r,
        {{"Mode", each "AER"},
         {"Cost", each null},
         {"Code", each #"AER Code"}
         }))),
   
    #"Insert the rows" = Table.Combine({tbl,#"Modified Rows to Insert"})
   
in
    #"Insert the rows"

Main Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table22"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"ID", Int64.Type}, {"Code", Int64.Type}, {"Assignment", Int64.Type}, {"AssignmentDesc", type text}, 
        {"Cost", Currency.Type}, {"Mode", type text}, {"AllCost", Currency.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {
        {"added Rows", each fnInsertAERrows(_), 
            type table [ID=Int64.Type, Code=Int64.Type, Assignment=Int64.Type, AssignmentDesc=nullable text, Cost=Currency.Type, Mode=nullable text, AllCost=Currency.Type]}}),
    
    #"Expanded added Rows" = Table.ExpandTableColumn(#"Grouped Rows", "added Rows", 
        {"Code", "Assignment", "AssignmentDesc", "Cost", "Mode", "AllCost"})
in
    #"Expanded added Rows"

Original Data

Results