Auto and Manual Categories

Hey Einomi ,

I just sending you the steps of M codes. Let's try the full M code with steps and comments.

let
    // STEP 1: Load and buffer your Mapping Table
    MappingTable = Excel.CurrentWorkbook(){[Name="tblMapping"]}[Content],
    MappingBuffered = Table.Buffer(MappingTable),

    // STEP 2: Load your main Transactions Table (with Category/Subcategory editable manually)
    TransactionsOriginal = Excel.CurrentWorkbook(){[Name="tblTransactions"]}[Content],

    // STEP 3: Self-merge to preserve manual inputs (Category/Subcategory)
    TransactionsBuffered = Table.Buffer(TransactionsOriginal),
    PreserveManualInputs = Table.RemoveColumns(
        Table.NestedJoin(TransactionsBuffered, {"Counter Party", "Reference"}, TransactionsBuffered, {"Counter Party", "Reference"}, "Manual", JoinKind.LeftOuter),
        "Manual"
    ),
    AddManualColumns = Table.ExpandTableColumn(
        Table.NestedJoin(TransactionsBuffered, {"Counter Party", "Reference"}, TransactionsBuffered, {"Counter Party", "Reference"}, "Manual", JoinKind.LeftOuter),
        "Manual",
        {"Category", "Subcategory"},
        {"ManualCategory", "ManualSubcategory"}
    ),

    // STEP 4: Add Auto Category/Subcategory from mapping
    AddAutoMappings = Table.AddColumns(
        AddManualColumns,
        {
            "AutoCategory", each try Record.Field(Table.SelectRows(MappingBuffered, (r) => Text.Upper(r[Description]) = Text.Upper([Counter Party])){0}, "Category") otherwise null,
            "AutoSubcategory", each try Record.Field(Table.SelectRows(MappingBuffered, (r) => Text.Upper(r[Description]) = Text.Upper([Counter Party])){0}, "Subcategory") otherwise null
        }
    ),

    // STEP 5: Decide Final Values → Manual wins over Auto
    AddFinalCategory = Table.AddColumn(AddAutoMappings, "FinalCategory", each if [ManualCategory] <> null and [ManualCategory] <> "" then [ManualCategory] else [AutoCategory]),
    AddFinalSubcategory = Table.AddColumn(AddFinalCategory, "FinalSubcategory", each if [ManualSubcategory] <> null and [ManualSubcategory] <> "" then [ManualSubcategory] else [AutoSubcategory]),

    // STEP 6: Output only relevant columns
    FinalOutput = Table.SelectColumns(AddFinalSubcategory, {"Date", "Counter Party", "Reference", "Amount", "FinalCategory", "FinalSubcategory"})

in
    FinalOutput

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam