Forum Discussion

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

Hey Einomi ,

Thank you for providing the screenshots and context. To help you reconcile automated and manual Category/Subcategory values into one seamless output for reporting while optimizing performance and minimizing merges.

  • Uses Table.Buffer to prevent repetitive evaluation,

  • Avoids multiple merges,

  • Keeps manual inputs intact,

  • Prioritizes auto-matching from your mapping table but allows override via manual entries.

Combined Logic: Merge Auto + Manual Inputs

let
    // Step 1: Buffer mapping table to improve performance
    MappingBuffered = Table.Buffer(tblMapping),

    // Step 2: Add Auto Category and Subcategory from mapping table
    AddAutoMappings = Table.AddColumns(
        tblTransactions,
        {
            "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 3: Merge Manual and Auto columns into Final ones
    AddFinalCategory = Table.AddColumn(AddAutoMappings, "FinalCategory", each if [Category] <> null and [Category] <> "" then [Category] else [AutoCategory]),
    AddFinalSubcategory = Table.AddColumn(AddFinalCategory, "FinalSubcategory", each if [Subcategory] <> null and [Subcategory] <> "" then [Subcategory] else [AutoSubcategory]),

    // Step 4: Keep only relevant columns for reporting
    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

Einomi's avatar
Einomi
Helper V
1 year ago

Hi,

Thanks for your time. Can you help with the full M code ? looks like it missing some code.

And if you can detail some steps, because we have to do self-merge a query if I am not mistaken to be able to input in Excel

Let me know

  • Nasif_Azam's avatar
    Nasif_Azam
    Super User
    1 year ago

    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

    • Einomi's avatar
      Einomi
      Helper V
      1 year ago

      Hi

      Thanks for your time but I am bit still confused.

      Can we write the M code from scratch becasue I do not see the original load of the transactions table.

      Thanks

      • Nasif_Azam's avatar
        Nasif_Azam
        Super User
        1 year ago

        Hey Einomi ,

        You want to:

        • Load your transaction table (CSV or Excel),

        • Map Categories/Subcategories automatically from a mapping table,

        • Retain any manual edits you make in Excel (so they're not lost after refresh),

        • Combine both auto and manual in a single output for reporting,

        • Avoid repeated merges and improve performance using Table.Buffer.

        Full M Code From Scratch

        let
    // STEP 1: Load the mapping table from Excel
    MappingTable = Excel.CurrentWorkbook(){[Name="tblMapping"]}[Content],
    MappingBuffered = Table.Buffer(MappingTable),

    // STEP 2: Load transactions from Excel (this is the table with editable Category/Subcategory)
    TransactionsRaw = Excel.CurrentWorkbook(){[Name="tblTransactions"]}[Content],
    TransactionsBuffered = Table.Buffer(TransactionsRaw),

    // STEP 3: Add auto Category/Subcategory using Mapping Table
    AddAutoColumns = Table.AddColumns(
        TransactionsBuffered,
        {
            "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 4: Final logic – Manual input takes priority over auto
    AddFinalCategory = Table.AddColumn(
        AddAutoColumns,
        "FinalCategory",
        each if [Category] <> null and [Category] <> "" then [Category] else [AutoCategory]
    ),

    AddFinalSubcategory = Table.AddColumn(
        AddFinalCategory,
        "FinalSubcategory",
        each if [Subcategory] <> null and [Subcategory] <> "" then [Subcategory] else [AutoSubcategory]
    ),

    // STEP 5: Keep necessary columns
    FinalOutput = Table.SelectColumns(
        AddFinalSubcategory,
        {"Date", "Counter Party", "Reference", "Amount", "FinalCategory", "FinalSubcategory"}
    )

in
    FinalOutput

        Things to remember:

        • The query does not overwrite manual values; any edits you make directly in the tblTransactions table will be retained.

        • The "tblMapping" and "tblTransactions" should be named Excel tables in your workbook.

         

        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