Auto and Manual Categories

try this 

let
    // load mapping table into dictoionary record
    auto = Function.Invoke(
        Record.FromList,
        List.Reverse(
            Table.ToColumns(
                Table.CombineColumnsToRecord(
                    Excel.CurrentWorkbook(){[Name="auto"]}[Content], 
                    "rec", 
                    {"Category", "Subcategory"}
                )
            )
        )
    ),
    // load old transactions, apply mapping to nulls
    loaded_transactions = Table.Buffer(Excel.CurrentWorkbook(){[Name="loaded"]}[Content]),
    add_mapping = Table.AddColumn(
        loaded_transactions, 
        "map", 
        (x) => if x[Category] is null and x[Subcategory] is null 
            then Record.FieldOrDefault(auto, x[Counter Party], []) 
            else Record.SelectFields(x, {"Category", "Subcategory"})
    ),
    remove_old_columns = Table.RemoveColumns(add_mapping, {"Category", "Subcategory"}),
    // load new transactions
    loaded_ref = Record.FromList(List.Repeat({false}, Table.RowCount(loaded_transactions)), List.Transform(loaded_transactions[ref], Text.From)),
    new_transactions = Table.AddColumn(
        Table.SelectRows(statement, (x) => Record.FieldOrDefault(loaded_ref, x[ref], true)),
        "map", 
        (x) => Record.FieldOrDefault(auto, x[Counter Party], [])
    ), 
    // combine old and new, expand cat/subcat columns
    combine = remove_old_columns & new_transactions,
    expand = Table.ExpandRecordColumn(combine, "map", {"Category", "Subcategory"}, null) 
in
    expand

Regarding index usage: again, I added index just for demo purposes. It plays "unique transaction reference" role in my file. Don't use index. Maybe you don't need unique ref at all - it depends upon the data you are loading. E.g. if you load historical data then you may use date column as you key. You need _something_ that identifies your transactions as "old" or "new".