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